CODEWITHSUNDEEP
sqlsql-server-2012
ThinkGeek
ThinkGeek

Reputation: 5127

Drop all the tables in a DB from Microsoft SQL server management studio

How do I drop all the tables in a DB from Microsoft SQL Server Management Studio? Is it possible with one query?

I don't want to click on individual tables or type the name of individual tables in the query to drop them.

Upvotes: 1

Views: 564

Answers (1)

HoneyBadger
HoneyBadger

Reputation: 15140

Here's what I sometimes use:

DECLARE @T NVARCHAR(255)
,       @S NVARCHAR(255)
,       @Type VARCHAR(255)
,       @SQL NVARCHAR(MAX)

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

DECLARE crs CURSOR FAST_FORWARD
FOR
SELECT      QUOTENAME(T.TABLE_SCHEMA)
,           QUOTENAME(T.TABLE_NAME)
,           T.TABLE_TYPE
FROM        INFORMATION_SCHEMA.TABLES T
-- Add where clause if you don't want to clear all schema's, or want to keep some tables.

OPEN crs
FETCH NEXT FROM crs INTO @S, @T, @Type
WHILE @@FETCH_STATUS = 0

BEGIN
    IF @Type = 'BASE TABLE'
        SET @SQL = CONCAT(N'DROP TABLE ', @S, '.',  @T)
    IF @Type = 'VIEW'
        SET @SQL = CONCAT(N'DROP VIEW ', @S, '.',  @T)

    EXEC sys.sp_executesql @SQL

    FETCH NEXT FROM crs INTO @S, @T, @Type
END

CLOSE crs
DEALLOCATE crs

Upvotes: 1

Related Questions