Deleting all tables and constraints in a database – sides of march (Brian DeMarzo)

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) &gt; 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) &gt; 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 = '%'.

Leave a Reply Cancel Reply