Cleaning a Database
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