Drop all tables whose names begin with a certain string

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.

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]%'

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

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

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.

Drop all tables whose names begin with a certain string

Related

Recent Posts