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 the 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 the above query contact to your database department.
