We all need to do it from time to time — erase a bunch of tables in a SQL database. Sure, you can do this through Enterprise Manager, one table at a time, or write a bunch of
DROP TABLE statements, but when doing it this way, you also need to ensure all foreign key constraints (i.e. table dependencies) are dropped first.
There’s an easier way — the following script, which was derived from a few posts scattered around the Internet. (Sorry, it’s been a while, and I don’t remember what they were!)
declare @sql varchar(max) declare @tbl varchar(255) declare @tblname varchar(255) --Specify the "like" clause for your tables here --to match all tables, use '%' set @tblname = '' select 'alter table ' + table_schema + '.' + table_name + ' drop constraint ' + constraint_name as query into #constraints from information_schema.table_constraints where constraint_type = 'foreign key' and table_name like @tblname while (select count(*) from #constraints) > 0 begin select top 1 @sql = query from #constraints exec (@sql) delete from #constraints where query = @sql end drop table #constraints select name into #tables from sys.objects where type = 'U' and name like @tblname while (select count(*) from #tables) > 0 begin select top 1 @tbl = name from #tables set @sql = 'drop table ' + @tbl exec (@sql) delete from #tables where name = @tbl end drop table #tables go
Simply change the
set @tblname = '' as needed — it’s used in a
LIKE statement to allow you to do these operations on a subset of tables. To run this script against all tables, use
set @tblname = '%'.