Dropping Extra Tables 1

[cclabaugh]

I'm looking for a way to query an existing table that has other valid table names that we are using in it and then compare those tables to the list of tables that actually exist in the db. If there are extras, I want to delete them. I tried to query my table and then delete all extra tables that were in the sysobjects table.
I can't get any code to work right.

 

[clapag22]

select 'drop table ' + name + '
GO'
from sysobjects
where type = 'u'
and name not in (select table_name from mytable)
and name != 'dtproperties'

Copy and paste the results into the query window and execute.

BE EXTREMELY CAREFUL! Audit the tables that will be dropped before actually dropping them to confirm they are not needed and are not system tables. I have seen system tables show up when doing a search in sysobjects for the 'u' type before...

 

[cclabaugh]

Thank you, that worked great.