Delete multiple tables in SQL Server by table names

2014-08-20


If data tables have similar table names, such as data log tables named by dates, you might want to delete multiple tables, for example, you wanted to delete all tables in a month, or in a year.

Here we have a way:

Use the following script:

SELECT 'DROP TABLE "' + TABLE_NAME + '"' 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'RC11%'

Run above script, you will get a result: a list of "drop table…" in result panel in SQL Server management studio. Now, right click in result, and select Save Result As…, save result to a text file.

image

Copy all text content in text file, and paste to a query window in SQL Server Management Studio, run it.

Now all specified tables should be deleted.

Be careful !, check all scripts before you execute generated script.