CODEWITHSUNDEEP
databasesql-server-2012
Momen Uthman
Momen Uthman

Reputation: 1

How can I drop multiple tables (but not all) in one database?

My case is : DBname : Groups, and temp tables created by a serial number each time the backend user creates a new group of glossary items (i.e. 1000, 10001, 10002...) How can I drop tables which have a serial number > 10002 ?

What I need is to drop the tables using the condition where the table name > 1002.

Upvotes: 0

Views: 48

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

In Sql Server the simplest way would probably be to generate dynamic sql using information_schema.tables:

DECLARE @Sql nvarchar(4000) = ''; -- you might need max instead of 4000 here...

 SELECT @Sql = @Sql + 'DROP TABLE '+ TABLE_NAME +';'
 FROM information_schema.tables
 WHERE TABLE_NAME LIKE 'glossary_items%'
 AND CAST(REPLACE(TABLE_NAME, 'glossary_items_', '') As int) > 1002;

EXEC(@Sql)

Assuming table names are glossary_items_XXXX where XXXX is the number (so glossary_items_1000, glossary_items_1001 etc')

Following Dan Guzman's comment - a better implementation would be using for xml for 2016 version (or earlier):

SET @Sql = (
    SELECT 'DROP TABLE '+ TABLE_NAME +';'
    FROM information_schema.tables
    WHERE TABLE_NAME LIKE 'glossary_items%'
    AND CAST(REPLACE(TABLE_NAME, 'glossary_items_', '') As int) > 1002;
    FOR XML PATH('')
)

or string_agg for 2017 version (or later):

SELECT @Sql = STRING_AGG('DROP TABLE '+ TABLE_NAME, ';')
FROM information_schema.tables
WHERE TABLE_NAME LIKE 'glossary_items%'
AND CAST(REPLACE(TABLE_NAME, 'glossary_items_', '') As int) > 1002;

Upvotes: 1

Related Questions