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

About Me Sample Title

This is a sample text about you. You may login and go to the Dojuniko settings page and edit this text. Here you can display a summary of your website or anything that is interesting to your visitors. You also can disable this section completely. You have full control thru the settings page.