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
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.