Mon 23 Jul 2007
Drop all foreign keys
Posted by tom under Programming
1 Comment
I was working on some tables today and found the need to drop all the foreign key constraints. There is no easy function in the manager so I whipped up this little bit of SQL that will create the commands needed to do this. This will work in MS Sql Server 2000 and 2005
SELECT ‘ALTER TABLE ‘ + TABLE_SCHEMA + ‘.’ + TABLE_NAME +
‘ DROP CONSTRAINT ‘ + CONSTRAINT_NAME
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = ‘FOREIGN KEY’
This should work for all weird table names:
SELECT ‘ALTER TABLE ‘ + TABLE_SCHEMA + ‘.[' + TABLE_NAME +
'] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']‘
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = ‘FOREIGN KEY’