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.
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.