CODEWITHSUNDEEP
sql-serverdatabase
UncleBob
UncleBob

Reputation: 1391

How to delete the contents of a database in SQL Server Management Studio?

Extremely noobish question, but I really am a bleeding beginner when it comes to databases and SQL Server in particular.

I created a database which I populated it with some test data, and now it's time to make room for the real data. Except, I cannot find an easy way to delete my old test data from the tables. And to my astonishment, google won't help me.

I found a multitude of posts about how to delete tables, but I still need those. It's only the data I don't need.

Drawing on my (very limited) experience with databases, though, deleting tables and recreating them seems to be a common method, except that I didn't find anything on how to recreate the table in SQL Server Managment Studio. Apart from the obvious "by hand" method, at least.

As it stands, I'm going through my tables in the right order deleting the rows manually, but that's hardly an economic way to do it in the future. So how can I get rid of all the data in the table (or every table, for that matter) while still having the table structure afterwards?

Upvotes: 0

Views: 16792

Answers (3)

A_Sk
A_Sk

Reputation: 4630

Try:

step1> generate Script of the database and save it in file(.sql)(without data)

step2> delete current database

step3> run the script(.sql) in Sql Server Management Studio

now you'll get the blank database.

see Generate a Script (SQL Server Management Studio)

Upvotes: 3

jhole89
jhole89

Reputation: 828

You can truncate the table (empties the contents/data but preserves the table structure) 

TRUNCATE TABLE MyTable

Upvotes: 5

Felix Pamittan
Felix Pamittan

Reputation: 31879

This will DELETE all rows from your table and RESEED your IDENTITY columns:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SET NOCOUNT ON;';

WITH Cte(tableName, hasIdentity) AS(
    SELECT t.name, CAST(ISNULL(ic.object_id, 0) AS BIT)
    FROM sys.tables t
    LEFT JOIN sys.identity_columns ic
        ON t.object_id = ic.object_id
    WHERE t.type = 'U'
)
SELECT @sql = @sql + CHAR(10) + 
    N'ALTER TABLE ' + QUOTENAME(tableName) + ' NOCHECK CONSTRAINT ALL;' + CHAR(10) +
    N'DELETE ' + QUOTENAME(tableName) + ';' + CHAR(10) + 
    CASE
        WHEN hasIdentity = 1 THEN 
            N'DBCC CHECKIDENT(''' + QUOTENAME(tableName) + ''', RESEED, 0) WITH NO_INFOMSGS;' + CHAR(10)
        ELSE ''
    END +
    N'ALTER TABLE ' + QUOTENAME(tableName) + ' WITH CHECK CHECK CONSTRAINT ALL;'
FROM Cte

PRINT @sql;
EXEC sp_executesql @sql;

Upvotes: 0

Related Questions