Check, Disable, Enable Foreign Key(s) – SQL Server

Posted: July 25, 2012 in SQLServer
Tags: ,

— Query to see all available foreign keys in the DB
SELECT
f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName,
f.is_disabled
FROM
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
where
f.name = ‘<FK Name>’

— Disable foreign key
ALTER TABLE <Table Name> NOCHECK CONSTRAINT <FK Name>
— Enable foreign key
ALTER TABLE <Table Name> CHECK CONSTRAINT <FK Name>

More references …

http://blog.sqlauthority.com/2007/09/04/sql-server-2005-find-tables-with-foreign-key-constraint-in-database/

http://www.mssqltips.com/sqlservertip/1376/disable-enable-drop-and-recreate-sql-server-foreign-keys/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s