Delete multiple tables in SQL Server by table names

Published on Wednesday, August 20, 2014

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.