Drop all tables whose names begin with a certain string

Question

How can I drop all tables whose names begin with a given string?

I think this can be done with some dynamic SQL and the INFORMATION_SCHEMA tables.

Uphroe

Answer 1

You may need to modify the query to include the owner if there's more than one in the database.

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

OPEN cmds
WHILE 1 = 1
BEGIN
    FETCH cmds INTO @cmd
    IF @@fetch_status != 0 BREAK
    EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds

This is cleaner than using a two-step approach of generate script plus run. But one advantage of the script generation is that it gives you the chance to review the entirety of what's going to be run before it's actually run.

I know that if I were going to do this against a production database, I'd be as careful as possible.

Edit Code sample fixed.

Touslesmois

You may have to run this script several times because of foreign key constraints between master and detail tables. – Bookcraft

In SQL Server 2005 I had to change the last two lines to close cmds; deallocate cmds. – Grumble

Warning: This solution may also delete tables created by SQL Server! My solution below avoids this and deletes tables in foreign key dependency order. – Indicant

This did not work for me. The answer to this question worked: #5116796 – Cacciatore

Answer 2

SELECT 'DROP TABLE "' + TABLE_NAME + '"' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '[prefix]%'

This will generate a script.

Adding clause to check existence of table before deleting:

SELECT 'IF OBJECT_ID(''' +TABLE_NAME + ''') IS NOT NULL BEGIN DROP TABLE [' + TABLE_NAME + '] END;' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '[prefix]%'

Wastage

I might add to remove the brackets when replacing "prefix" with your target prefix. – Fancied

MYSQL: SELECT concat('DROP TABLE ',TABLE_NAME,";") as data FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '[prefix]%' --- for those who like me found this thread – Turaco

The result contains also views – Physostomous

Don't forget to escape _ if that's part of your prefix, eg. WHERE TABLE_NAME LIKE 'em\_%' ESCAPE '\'; – Lilalilac

This generates a script, but how does one execute the script? – Biotin

Awesome! I really appreciate the script first approach so I could see what was going to drop as opposed to just executing. Super helpful thanks!! – Stylist

With this workflow, execute the script by highlighting the full output column and copy & paste it into your query window. The idea is to manually review each line and perhaps modify before executing the drops. – Libove

` [42883] ERROR: operator does not exist: unknown + information_schema.sql_identifier [2023-07-12 16:00:12] Hint: No operator matches the given name and argument types. You might need to add explicit type casts.` I'm getting this error. How to fix it? – Mathian

Answer 3

This will get you the tables in foreign key order and avoid dropping some of the tables created by SQL Server. The t.Ordinal value will slice the tables into dependency layers.

WITH TablesCTE(SchemaName, TableName, TableID, Ordinal) AS
(
    SELECT OBJECT_SCHEMA_NAME(so.object_id) AS SchemaName,
        OBJECT_NAME(so.object_id) AS TableName,
        so.object_id AS TableID,
        0 AS Ordinal
    FROM sys.objects AS so
    WHERE so.type = 'U'
        AND so.is_ms_Shipped = 0
        AND OBJECT_NAME(so.object_id)
        LIKE 'MyPrefix%'

    UNION ALL
    SELECT OBJECT_SCHEMA_NAME(so.object_id) AS SchemaName,
        OBJECT_NAME(so.object_id) AS TableName,
        so.object_id AS TableID,
        tt.Ordinal + 1 AS Ordinal
    FROM sys.objects AS so
        INNER JOIN sys.foreign_keys AS f
            ON f.parent_object_id = so.object_id
                AND f.parent_object_id != f.referenced_object_id
        INNER JOIN TablesCTE AS tt
            ON f.referenced_object_id = tt.TableID
    WHERE so.type = 'U'
        AND so.is_ms_Shipped = 0
        AND OBJECT_NAME(so.object_id)
        LIKE 'MyPrefix%'
)
SELECT DISTINCT t.Ordinal, t.SchemaName, t.TableName, t.TableID
FROM TablesCTE AS t
    INNER JOIN
    (
        SELECT
            itt.SchemaName AS SchemaName,
            itt.TableName AS TableName,
            itt.TableID AS TableID,
            Max(itt.Ordinal) AS Ordinal
        FROM TablesCTE AS itt
        GROUP BY itt.SchemaName, itt.TableName, itt.TableID
    ) AS tt
        ON t.TableID = tt.TableID
            AND t.Ordinal = tt.Ordinal
ORDER BY t.Ordinal DESC, t.TableName ASC

Rowlock

Thanks to #352676 – Indicant

Quick fix: TableName appears a few times in WHERE clauses and should be replaced with OBJECT_NAME(so.object_id). Nice script! – Arnuad

Answer 4

On Oracle XE this works:

SELECT 'DROP TABLE "' || TABLE_NAME || '";'
FROM USER_TABLES
WHERE TABLE_NAME LIKE 'YOURTABLEPREFIX%'

Or if you want to remove the constraints and free up space as well, use this:

SELECT 'DROP TABLE "' || TABLE_NAME || '" cascade constraints PURGE;'
FROM USER_TABLES
WHERE TABLE_NAME LIKE 'YOURTABLEPREFIX%'

Which will generate a bunch of DROP TABLE cascade constraints PURGE statements...

For VIEWS use this:

SELECT 'DROP VIEW "' || VIEW_NAME || '";'
FROM USER_VIEWS
WHERE VIEW_NAME LIKE 'YOURVIEWPREFIX%'

Morisco

Worked perfectly. Had 61,037 empty tables to delete from a db used for QA. I used the cascade constraints example. Generated the output then copied it all into a script and ran it. Took forever but it worked like a charm! Thanks! – Collencollenchyma

Answer 5

Here is my solution:

SELECT CONCAT('DROP TABLE `', TABLE_NAME,'`;') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'TABLE_PREFIX_GOES_HERE%';

And of course you need to replace TABLE_PREFIX_GOES_HERE with your prefix.

Deliciadelicious

you run the command to get the list of drops, then run the drops command. it is a copy + paste (and then search+replace the | character) but it does the job well ! Thanks :) – Paraformaldehyde

i can't edit my reply above, but if you run the mysql client in silent mode (in my case i use drush so i would go drush sql-cli --extra=-s ) then you can just copy+paste from the command line =) – Paraformaldehyde

Answer 6

EXEC sp_MSforeachtable 'if PARSENAME("?",1) like ''%CertainString%'' DROP TABLE ?'

Edit:

sp_MSforeachtable is undocumented hence not suitable for production because it's behavior may vary depending on MS_SQL version.

Try

Awesome one-liner! This should be voted to the top. – Solfa

Answer 7

I saw this post when I was looking for mysql statement to drop all WordPress tables based on @Xenph Yan here is what I did eventually:

SELECT CONCAT(  'DROP TABLE `', TABLE_NAME,  '`;' ) AS query
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE  'wp_%'

this will give you the set of drop queries for all tables begins with wp_

Millibar

Answer 8

CREATE PROCEDURE usp_GenerateDROP
    @Pattern AS varchar(255)
    ,@PrintQuery AS bit
    ,@ExecQuery AS bit
AS
BEGIN
    DECLARE @sql AS varchar(max)

    SELECT @sql = COALESCE(@sql, '') + 'DROP TABLE [' + TABLE_NAME + ']' + CHAR(13) + CHAR(10)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME LIKE @Pattern

    IF @PrintQuery = 1 PRINT @sql
    IF @ExecQuery = 1 EXEC (@sql)
END

Zoller

Answer 9

Xenph Yan's answer was far cleaner than mine but here is mine all the same.

DECLARE @startStr AS Varchar (20)
SET @startStr = 'tableName'

DECLARE @startStrLen AS int
SELECT @startStrLen = LEN(@startStr)

SELECT 'DROP TABLE ' + name FROM sysobjects
WHERE type = 'U' AND LEFT(name, @startStrLen) = @startStr

Just change tableName to the characters that you want to search with.

Bourgogne

Answer 10

This worked for me.

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 'something%';

PRINT @sql;
-- EXEC sp_executesql @sql;

Denotative

Answer 11

select 'DROP TABLE ' + name from sysobjects
where type = 'U' and sysobjects.name like '%test%'

-- Test is the table name

Julide

this doesn't actually execute anything, just return a bunch of commands. – Brilliancy

Answer 12

SELECT 'if object_id(''' + TABLE_NAME + ''') is not null begin drop table "' + TABLE_NAME + '" end;' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '[prefix]%'

Jubilant

Answer 13

I had to do a slight derivation on Xenph Yan's answer I suspect because I had tables not in the default schema.

SELECT 'DROP TABLE Databasename.schema.' + TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'strmatch%'

Tove

Answer 14

In case of temporary tables, you might want to try

SELECT 'DROP TABLE "' + t.name + '"' 
FROM tempdb.sys.tables t
WHERE t.name LIKE '[prefix]%'

Cherokee

Answer 15

I would like to post my proposal of the solution which DROP (not just generate and select a drop commands) all tables based on the wildcard (e.g. "table_20210114") older than particular amount of days.

DECLARE 
    @drop_command NVARCHAR(MAX) = '',
    @system_time date,
    @table_date nvarchar(8),
    @older_than int = 7
    
Set @system_time = (select getdate() - @older_than)
Set @table_date = (SELECT CONVERT(char(8), @system_time, 112))

SELECT @drop_command += N'DROP TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME([Name]) + ';'
FROM <your_database_name>.sys.tables
WHERE [Name] LIKE 'table_%' AND RIGHT([Name],8) < @table_date

SELECT @drop_command
 
EXEC sp_executesql @drop_command

Kenweigh

Answer 16

If your query returns more than one line, you can collect the results and merge them into a query.

declare @Tables as nvarchar(max) = '[schemaName].['
select @Tables =@Tables + TABLE_NAME +'],[schemaName].['
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_SCHEMA = 'schemaName'
AND TABLE_NAME like '%whateverYourQueryIs%'

select @Tables =  Left(@Tables,LEN(@Tables)-13) --trying to remove last ",[schemaName].[" part, so you need to change this 13 with actual lenght 

--print @Tables

declare @Query as nvarchar(max) = 'Drop table ' +@Tables 

--print @Query


exec sp_executeSQL @Query

Dolphin

Answer 17

Try following code:

declare @TableLst table(TblNames nvarchar(500))
insert into @TableLst (TblNames)
SELECT 'DROP TABLE [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'yourFilter%'
WHILE ((select COUNT(*) as CntTables from @TableLst) > 0)
BEGIN
    declare @ForExecCms nvarchar(500) = (select top(1) TblNames from @TableLst)
    EXEC(@ForExecCms)
    delete from @TableLst where TblNames = @ForExecCms
END

This SQL script is executed without using a cursor.

Taggart

Answer 18

If you suddenly need to delete tables linked by foreign keys.

USE [CentralIntake]
GO

DECLARE @name VARCHAR(200);
DECLARE @DropForeignKeyProcedure varchar(4000); 
DECLARE @DropTableProcedure varchar(4000); 

/*TEST*/ SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE '%_unused'

DECLARE tb_cursor CURSOR FOR 
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE '%_unused';

OPEN tb_cursor  
FETCH NEXT FROM tb_cursor INTO @name 

WHILE @@FETCH_STATUS = 0  
BEGIN  

/*TEST*/ SELECT 'ALTER TABLE [' +  OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']'    FROM sys.foreign_keys WHERE referenced_object_id = object_id(@name)

    DECLARE fk_cursor CURSOR FOR
    (SELECT 'ALTER TABLE [' +  OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']'
    FROM sys.foreign_keys
    WHERE referenced_object_id = object_id(@name));

    OPEN fk_cursor
    FETCH NEXT FROM fk_cursor INTO @DropForeignKeyProcedure
    WHILE @@FETCH_STATUS = 0  
        BEGIN   
            EXEC (@DropForeignKeyProcedure);
            FETCH NEXT FROM fk_cursor INTO @DropForeignKeyProcedure
        END
    CLOSE fk_cursor  
    DEALLOCATE fk_cursor

    SET @DropTableProcedure = (SELECT 'DROP TABLE [' +  TABLE_CATALOG  + '].[' + TABLE_SCHEMA + '].[' + @name + ']'
    FROM INFORMATION_SCHEMA.TABLES
    where TABLE_NAME = @name)

    EXEC(@DropTableProcedure)

    FETCH NEXT FROM tb_cursor INTO @name 
END 

CLOSE tb_cursor  
DEALLOCATE tb_cursor

Teddy