The less-than-random ramblings of Scott Jackson, software architect.

No Such Comfort

Cleaning a Database

Posted by scottj on June 28, 2012 in Debugging with No Comments


When developing against SQL Server, sometimes scripts accidentally get run against the [MASTER] database.  I created the following script to drop all foreign keys, views and tables from a database.  It worked in my case, but be very careful with it.  It has two checks (called out in red) in there for tables that were not part of my solution, and they may not cover every case.  If someone knows how to refine this to exclude all sytem tables, I would appreciate them correcting this in the comments.  Thanks in advance.

select 'ALTER TABLE [' + c.TABLE_SCHEMA + '].[' + c.TABLE_NAME + ']'
  + ' DROP CONSTRAINT [' + c.CONSTRAINT_NAME + ']'
from information_schema.tables t
join information_schema.table_constraints c
 on t.table_catalog = c.table_catalog
 and t.table_schema = c.table_schema
 and t.table_name = c.table_name
where t.table_name not like 'MS%' and t.table_name not like 'spt_%'
 and c.constraint_type = 'foreign key'
union
select 'DROP TABLE [' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']'
from information_schema.tables t
where t.table_name not like 'MS%'
 and t.table_name not like 'spt_%'
 and t.table_type = 'base table'
union
select 'DROP VIEW [' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']'
from information_schema.tables t
where t.table_name not like 'MS%'
 and t.table_name not like 'spt_%'
 and t.table_type = 'view'
Leave a Reply

No trackbacks yet.

No post with similar tags yet.

Posts in similar categories

Capricious Whim

“Einstein argued that there must be simplified explanations of nature, because God is not capricious or arbitrary. No such faith comforts the software engineer.” —Fred Brooks