Drop All Tables in a Database

So I recently had to delete all tables in a database based on schema name. Here is the solution I came up with:

DECLARE @tblName VARCHAR(200) ;

 

DECLARE tbl CURSOR

FOR

SELECT SCHEMA_NAME(schema_id) + ‘.’ + name AS tableName

FROM sys.tables

WHERE schema_id IN (SCHEMA_ID(‘admdb’), SCHEMA_ID(<Your Schema Here>), SCHEMA_ID(<Your Schema Here>)) ;

 

OPEN tbl ;

 

FETCH NEXT FROM tbl INTO @tblName ;

WHILE @@FETCH_STATUS=0

BEGIN

EXEC(‘DROP TABLE ‘ + @tblName) ;

 

FETCH NEXT FROM tbl INTO @tblName ;

END

 

CLOSE tbl ;

DEALLOCATE tbl ;

 

A couple things worth mentioning is that I set the table name to 200 character varchar. Of course this script can do some damage, please, please, please make sure you don’t need anything in any of the tables before running this!

By default, drop table does not accept a variable but if you turn it into a dynamic query (wrapping it in an execute function) works perfectly.

Advertisement