Droping tables that are found via select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Droping tables that are found via select statement

Author

Topic

Bazinga
Starting Member

19 Posts

Posted - 2011-12-01 : 14:10:28

I have generated a simple select statement that identifies tables in the database that should be removed. The tables were originally created based on two fields in a separate table, thus the selection criteria combining those fields together as follows

SELECT rtrim(report_owner)+ '.' + rtrim(report_table)
FROM report_descripts
WHERE report_run_date <= '2005-07-01'

The results are a list of tables like:
jim.report1
bob.reportname3
etc

Now I want to drop the tables from the select statement results, but I'm not sure how to make it work. Do I just simply use the DROP TABLE command and if so, where do I place it?

Thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-01 : 14:16:23

What I do usually is to generate the drop statements via the select, copy and paste the output of the select and execute it. So in your example run this:

SELECT 'DROP TABLE ' + rtrim(report_owner)+ '.' + rtrim(report_table)
FROM report_descripts
WHERE report_run_date <= '2005-07-01'

Then, look at the output, verify that that is exactly what you want to do, check it again two more times, then copy that to a SQL query window and run it.

Bazinga
Starting Member

19 Posts

Posted - 2011-12-01 : 14:31:55

That worked. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources