Drop multiple tables in a table using dynamic sql

Question

I use PDW and I have query which creates a list of tables to be dropped.

Table Q :

TableName
---------
   a
   b
   c
   d
   e

a,b,c,d,e are the list of tables in the database which should be dropped.

I want to write a dynamic sql query which will drop the tables a to e listed in table Q without any human intervention.

Is it possible?

So the list of tables to drop is in another table? Something sounds like the design got derailed somewhere. And not really sure what you mean about no human intervention. At some level there has to be human intervention. — Sean Lange
– Sean Lange, Commented May 30, 2018 at 21:24
The path to your solution starts here. spaghettidba.com/2015/04/24/… — Sean Lange
– Sean Lange, Commented May 30, 2018 at 21:24

Hadi · Accepted Answer · 2018-05-31 07:33:48Z

1

You can use the following dynamic sql to achieve this: (assuming that the table name is Table Q and the column name is TABLE_NAME)

DECLARE @strQuery as varchar(MAX)

SET @strQuery = ''

SELECT @strQuery = @strQuery + 'DROP TABLE [' + TABLE_NAME + '];' FROM [Table Q] 

EXEC(strQuery)

edited May 31, 2018 at 7:33

answered May 31, 2018 at 1:19

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Erick de Vathaire Over a year ago

Improvement: Let this is a job so there is no human intervention

Collectives™ on Stack Overflow

Drop multiple tables in a table using dynamic sql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related