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.

 SELECT ‘ALTER TABLE ‘ +
       ‘[‘ + s.name + ‘].[‘ + t.name + ‘]’ +
       ‘ DROP CONSTRAINT [‘ + f.name +’]’
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.

image

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

Advertisements

Blog at WordPress.com.