Skip to main content

How to drop multiple tables with common prefix in one query?

Problem: Suppose we have a situation where we need to drop those tables that have some prefixes string, so it is possible to drop those tables with a common prefix in a single query.

Solution: yes it is possible to drop all those tables that have the common prefix in a single query. Using following query you can delete all those tables that begin with a certain prefix string. In where condition just put the common prefix string in where condition (Like ‘prefix%’)

DECLARE @query NVARCHAR(MAX) = N'';

SELECT @query += '
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 'MX_100%';

EXEC sp_executesql @query;

This query may create an issue, if a table has a foreign key relationship, you'll either need to drop them first or arrange the output to drop the tables in a certain order.
If you want to monitor exactly what goes on when the query is running then use the following query. 

DECLARE @query varchar(4000)
DECLARE csr CURSOR FOR
SELECT 'drop table [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE '<prefix>%'

OPEN csr
WHILE 1 = 1
BEGIN
    FETCH csr INTO @query
    IF @@fetch_status != 0 BREAK
    EXEC@query)
END
CLOSE csr;
DEALLOCATE csr


Note: If you are not a Database developer then don’t use above query contact to your database department.