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.

Advertisements

Have an opinion? Leave an opinion!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s