Drop Tables by Prefix in SQL Server and Azure SQL Database

To drop tables by prefix, list the matching tables first. Then drop them with a script that quotes every schema and table name. A real temporary table, named with a # sign, disappears with its session. A table that only starts with _Temp_ is an ordinary table and stays until you drop it.

Build the Demo Tables

Some jobs create work tables with a naming prefix, because the tables must outlive one session. The cleanup then has to find them by name.

The demo script creates five tables. Two of the work tables are in the dbo schema and one is in a schema named shop. A fourth table, XTemp_Orders, only looks similar. The fifth is a normal customer table that must survive.

IF DB_ID(N'PrefixDropDemo') IS NULL CREATE DATABASE PrefixDropDemo;
GO
USE PrefixDropDemo;
GO
IF SCHEMA_ID(N'shop') IS NULL EXEC (N'CREATE SCHEMA shop');
GO
DROP TABLE IF EXISTS dbo._Temp_, dbo._Temp_Prices, shop._Temp_Log, dbo.XTemp_Orders, dbo.Customers;
CREATE TABLE dbo._Temp_ (ID int);
CREATE TABLE dbo._Temp_Prices (ID int);
CREATE TABLE shop._Temp_Log (ID int);
CREATE TABLE dbo.XTemp_Orders (ID int);
CREATE TABLE dbo.Customers (ID int);

The Underscore Is a Wildcard

In a LIKE pattern, an underscore matches any one character. The pattern _Temp_% therefore matches more than names that start with _Temp_. The next query shows both patterns side by side. The escaped pattern puts a backslash before each underscore and names it as the escape character.

SELECT SCHEMA_NAME(schema_id) AS SchemaName,
       name AS TableName,
       CASE WHEN name LIKE N'_Temp_%' THEN 1 ELSE 0 END AS MatchesUnescaped,
       CASE WHEN name LIKE N'\_Temp\_%' ESCAPE N'\' THEN 1 ELSE 0 END AS MatchesEscaped
FROM sys.tables
ORDER BY SchemaName, TableName;

The unescaped pattern catches XTemp_Orders, a table you want to keep. Drop tables by prefix with the unescaped pattern, and that table goes with them. Always preview the list, and escape the underscore.

Drop the Tables in One Statement

STRING_AGG builds one DROP TABLE statement for the whole list. It needs SQL Server 2017 or later, and it works in Azure SQL Database. Each name is wrapped in QUOTENAME with its schema, so a table outside dbo drops as well. The script prints the statement as it runs it. The earlier SELECT is the preview, and the transaction rehearsal below is the safety net.

DECLARE @list nvarchar(max) = (
    SELECT STRING_AGG(CONVERT(nvarchar(max), QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(name)), N', ')
    FROM sys.tables
    WHERE name LIKE N'\_Temp\_%' ESCAPE N'\');
DECLARE @sql nvarchar(max) = N'DROP TABLE IF EXISTS ' + @list + N';';
PRINT @sql;
IF @list IS NOT NULL EXEC (@sql);

The printed statement lists three tables. The customer table and the lookalike stay. DROP TABLE IF EXISTS needs SQL Server 2016. It skips a name that is already gone, so the script can run twice.

Drop the Tables With a Cursor

A cursor does the same job one table at a time. It works on versions before STRING_AGG, and it lets you log or skip a table inside the loop. The loop ends when the cursor has no rows left, so it cannot run forever. First, create two work tables again.

CREATE TABLE dbo._Temp_Old (ID int);
CREATE TABLE shop._Temp_Log (ID int);

DECLARE @schema sysname, @name sysname, @sql nvarchar(max);
DECLARE work_tables CURSOR LOCAL FAST_FORWARD FOR
    SELECT SCHEMA_NAME(schema_id), name
    FROM sys.tables
    WHERE name LIKE N'\_Temp\_%' ESCAPE N'\';
OPEN work_tables;
FETCH NEXT FROM work_tables INTO @schema, @name;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'DROP TABLE IF EXISTS ' + QUOTENAME(@schema) + N'.' + QUOTENAME(@name) + N';';
    PRINT @sql;
    EXEC (@sql);
    FETCH NEXT FROM work_tables INTO @schema, @name;
END;
CLOSE work_tables;
DEALLOCATE work_tables;

Test the Drop Inside a Transaction

A DROP TABLE can be rolled back in SQL Server. That gives you a cheap rehearsal. Create the tables, start a transaction, drop them, count what is left, and roll back. The counts are 3, 0 and 3.

CREATE TABLE dbo._Temp_ (ID int);
CREATE TABLE dbo._Temp_Prices (ID int);
CREATE TABLE shop._Temp_Log (ID int);
SELECT COUNT(*) AS BeforeDrop FROM sys.tables WHERE name LIKE N'\_Temp\_%' ESCAPE N'\';
BEGIN TRANSACTION;
DROP TABLE IF EXISTS dbo._Temp_, dbo._Temp_Prices, shop._Temp_Log;
SELECT COUNT(*) AS InsideTransaction FROM sys.tables WHERE name LIKE N'\_Temp\_%' ESCAPE N'\';
ROLLBACK TRANSACTION;
SELECT COUNT(*) AS AfterRollback FROM sys.tables WHERE name LIKE N'\_Temp\_%' ESCAPE N'\';

Rollback brings every table back. Use the same pattern on your own server. Run the real drop, read the result, and roll back until you trust the list. Then run it for real.

Cautions

A table that another table refers to with a foreign key cannot be dropped first. SQL Server stops with Msg 3726. In that case, drop the child tables first, or drop the foreign keys. My post on how to drop multiple tables with one DROP TABLE statement shows that error and its fix.

Work objects also have an age. The column create_date in sys.tables tells you when each table was made. Add a filter such as create_date older than a day. Then the script never drops a table that a running job made a minute ago.

The same pattern drops work procedures. Replace sys.tables with sys.procedures, and the statement with DROP PROCEDURE IF EXISTS. Keep the escape, the QUOTENAME and the preview.

Check the database name before you run any drop script. In Azure SQL Database you connect to one database, so the connection decides where the script runs. Print DB_NAME() first, and read it.

You could argue that a name prefix is a poor filter, and that a separate schema is safer. It is. Put the work tables in a schema such as work, and the cleanup selects every table in that schema. No pattern can match a table by accident.

What to Remember

To drop tables by prefix, escape the underscore, preview the list and build each name with QUOTENAME and its schema. Use STRING_AGG when you can, and a cursor when you cannot. Test the drop inside a transaction first.

Prefer a dedicated schema over a prefix for work tables. When you finish the demo, run the cleanup script.

USE master;
GO
IF DB_ID(N'PrefixDropDemo') IS NOT NULL
BEGIN
    ALTER DATABASE PrefixDropDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE PrefixDropDemo;
END;

A name prefix is not a safe filter, it is a guess you must check before you drop.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.