Dropping table based on other table's Name

Question

Currently, I am basically dropping table like drop table scratch..TableName

If I have name of all the table in other separate table, then can I do perform to drop table by joining instead of copying and pasting name every time?

So I want to do something like this.. but I am no sure how to write this to SQL. I do use 2008 version. Table name matches from TableName table, then delete from scratch database.

Wanting to do this indicates you might be "doing sql" wrong. You don't usually go around creating/dropping tables with names determined at runtime. Having said that, you *can* do this with dynamic SQL. You need to build a string consisting of a bunch of `drop` statements and then execute it as SQL. See [this question](http://stackoverflow.com/questions/4393/drop-all-tables-whose-names-begin-with-a-certain-string) for some inspiration. — Blorgbeard, Jun 28 '16 at 02:27

score 0 · Answer 1 · answered Jun 28 '16 at 04:14

You can easily create a cursor that loops through the rows of your table and dynamically creates and executes a drop statement for every table. Something like this should do the trick:

DECLARE @i_Current_Drop_Statement NVARCHAR(MAX)

DECLARE Drop_Table_Statements CURSOR LOCAL FAST_FORWARD
    FOR 
    SELECT N'DROP TABLE ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name)
    FROM sys.tables t --Or replace this with your table that contains the tables to drop

OPEN Drop_Table_Statements
FETCH NEXT FROM Drop_Table_Statements INTO @i_Current_Drop_Statement

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_executesql @i_Current_Drop_Statement

    FETCH NEXT FROM Drop_Table_Statements INTO @i_Current_Drop_Statement
END

CLOSE Drop_Table_Statements
DEALLOCATE Drop_Table_Statements

Dropping table based on other table's Name

1 Answers1