Irfan's World

October 12, 2011

Drop Foreign Key Constraints in SQL Server 2008

Filed under: SQL Server 2000/2005/2008 — Tags: , , — Irfan @ 4:54 pm

The following SQL generates ALTER TABLE DROP CONSTRAINT sql for each table in the Database.  This script is used mostly when you try to TRUNCATE the tables in a database.

       ‘[‘ + + ‘].[‘ + + ‘]’ +
       ‘ DROP CONSTRAINT [‘ + +’]’
FROM sys.foreign_keys f
INNER JOIN sys.tables t ON f.parent_object_id=t.object_id
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.is_ms_shipped=0;

The ALTER TABLE sql statements that it has generated on my database is shown below. I made the image little blurred on purpose.


Don’t forget to press Ctrl+T to generate the sql in Text format in SSMS.

Blog at