How to drop multiple tables with common prefix in one query?
You can build up a string using the catalog views, e.g.:
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += '
DROP TABLE '
+ QUOTENAME(s.name)
+ '.' + QUOTENAME(t.name) + ';'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE t.name LIKE 'LG_001%';
PRINT @sql;
-- EXEC sp_executesql @sql;
Of course there are potential gotchas, for example if these tables have foreign key relationships, you'll either need to drop them first, or arrange the output to drop the tables in a certain order.
To just get the list of tables, use:
SELECT s.name, t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE t.name LIKE 'LG_001%';
I ran this query then pasted the results back into query window to drop all the tables:
SELECT 'DROP TABLE ' + NAME from sys.tables
ORDER BY NAME
If you want to delete all tables but keep those with names that started with A, B, C or D:
SELECT 'DROP TABLE ' + NAME from sys.tables
WHERE NAME NOT LIKE '[ABCD]%'
GROUP BY NAME