select
'ALTER TABLE ' + so.table_name + ' DROP CONSTRAINT ' + so.constraint_name
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS so
Friday, April 3, 2009
MSSQL Drop all constraints from the database
Recently I had to drop all tables from a MSSQL Server Database, and this proved to be harder than expected because of foreign key contraints in the database. I found a post on how do disable them but that turned out to be not enough to allow to drop the tables. Instead I had to drop all the constraints from the database. The few lines below generate the commands to do this.
Subscribe to:
Post Comments (Atom)
6 comments:
Nice!
Thanks! You saved me. ;-)
thx :)
thank you!
Saved a lot of time
THANKS
Its REALLY GOOD QUERY TO GET SQL STATEMENTS TO REMOVE CONSTRAINTS FROM TABLE.
2 Things to be taken care
1) Please dont remove all constraints unless its very much required.
2)Remove only intended, select them like
select
'ALTER TABLE ' + so.table_name + ' DROP CONSTRAINT ' + so.constraint_name
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS so where so.TABLE_NAME ='myTableName'
The article below will drop all foreign keys and optionally drop all tables.
http://www.sentientbeings.com/2012/01/drop-all-foreign-keys-on-a-database-and-optionally-drop-all-tables/
Post a Comment