Deleting all tables and constraints in a database

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
select top 1 @sql = query from #constraints
exec (@sql)
delete from #constraints where query = @sql

drop table #constraints

select name
into #tables
from sys.objects
where type = 'U'
and name like @tblname

while (select count(*) from #tables) > 0
select top 1 @tbl = name from #tables
set @sql = 'drop table ' + @tbl
exec (@sql)
delete from #tables where name = @tbl

drop table #tables

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

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.