Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Assuming I have a database column containing table names, how do I safely delete all of the tables it lists?

The tables themselves are fairly simple - they may have indeces, but definitely don't contain other complex items (and don't have dependencies on other tables - ie, foreign keys into/out of them, etc...)

share|improve this question

2 Answers 2

up vote 4 down vote accepted

Something along the lines of the following should work.

CREATE TABLE dbo.TablesToDrop
(
    TableName SYSNAME
);
INSERT INTO dbo.TablesToDrop VALUES ('Table1');
INSERT INTO dbo.TablesToDrop VALUES ('Table2');
INSERT INTO dbo.TablesToDrop VALUES ('Table3');
DECLARE @Cmd NVARCHAR(MAX);
SET @Cmd = '';
SELECT @Cmd = @Cmd + 'DROP TABLE ' + QUOTENAME(t.TableName) + ';'
FROM dbo.TablesToDrop T;
RAISERROR (@cmd, 0, 1) WITH NOWAIT;
--EXEC sp_executesql @Cmd;

Uncomment the last line to actually execute the DROP TABLE statements.

share|improve this answer
    
Please read this answer by Martin Smith, then stop using variable concatenation, even for 'simple' uses. It's a bad habit you should kick :) –  Paul White May 17 at 7:39

if your database column contains below table names:

column_name 
------------ 
test_kin1 
test_kin2 
test_kin3

Then you can use:

select 'drop table '+quotename(column_name) + char(10) +'go'from table_name

In text mode it will generate sql to drop them. Execute the generated sql by VERIFYING that you indeed want to drop the tables in another query window.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.