Generate Script for Foreign key’s in SQL Server.

During conducting one of the Proof Of Concept around “InMemory“, we had to disable foreign key to use Migration wizard provided by SSMS.
Before disable, we had to create script for all foreign key for multiple tables in Database and stored it as backup for later alteration.

As usual process, starting searching on web to get SQL to generate scripts for Foreign Keys. But was able to find only standard TSQL’s or Procedure.
As a Favoritism towards pure SQL, had compiled generation\alter\drop scripts around foreign key in pure SQL form.

Script to Generate Alter command for all Foreign key in SQL Server.

WITH Generator AS
(
SELECT
parent_object_id,
name,
referenced_object_id,
object_id,
is_disabled,
is_not_for_replication,
is_not_trusted,
delete_referential_action,
update_referential_action,
STUFF((SELECT '' + COL_NAME(fk.parent_object_id, fkc.parent_column_id)
from sys.foreign_key_columns fkc
where fkc.constraint_object_id = fk.object_id
ORDER BY fkc.constraint_column_id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,0,'') parentCols ,
STUFF((SELECT '' + COL_NAME(fk.referenced_object_id, fkc.referenced_column_id)
from sys.foreign_key_columns fkc
where fkc.constraint_object_id = fk.object_id
ORDER BY fkc.constraint_column_id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,0,'') referCols
FROM sys.foreign_keys as fk
)
SELECT 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id))
+ CASE WHEN is_not_trusted = 0 THEN ' WITH CHECK ' ELSE ' WITH NOCHECK ' END + ' ADD CONSTRAINT ' +
QUOTENAME(name) + ' FOREIGN KEY ( ' + QUOTENAME(parentCols) + ' ) REFERENCES ' +
QUOTENAME(OBJECT_SCHEMA_NAME(referenced_object_id)) + '.' + QUOTENAME( OBJECT_NAME(referenced_object_id))
+ ' (' + QUOTENAME(referCols) + ') '
+ ' ON UPDATE ' + CASE update_referential_action WHEN 0 THEN 'NO ACTION ' WHEN 1 THEN 'CASCADE ' WHEN 2 THEN 'SET NULL '
ELSE 'SET DEFAULT ' END +
' ON DELETE ' + CASE delete_referential_action WHEN 0 THEN 'NO ACTION ' WHEN 1 THEN 'CASCADE '
WHEN 2 THEN 'SET NULL ' ELSE 'SET DEFAULT ' END
+ CASE is_not_for_replication WHEN 1 THEN ' NOT FOR REPLICATION ' ELSE '' END + ';'
AS create_fk_script
FROM Generator
ORDER BY QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) , QUOTENAME(OBJECT_NAME(parent_object_id)) ;

Accumulated Script for Drop , Disable and Enable foreign key as well.

SELECT 'ALTER TABLE [' + SCHEMA_NAME(T.schema_id) + '].[' + T.name + '] DROP CONSTRAINT ' + OBJECT_NAME(FK.constraint_object_id) + ';' AS DROP_CONSTRAINT ,
'ALTER TABLE [' + SCHEMA_NAME(T.schema_id) + '].[' + T.name + '] NOCHECK CONSTRAINT ' + OBJECT_NAME(FK.constraint_object_id) + ';' AS DISABLE_CONSTRAINT ,
'ALTER TABLE [' + SCHEMA_NAME(T.schema_id) + '].[' + T.name + '] CHECK CONSTRAINT ' + OBJECT_NAME(FK.constraint_object_id) + ';' AS ENABLE_CONSTRAINT
FROM sys.foreign_key_columns AS FK INNER JOIN
sys.tables AS T ON FK.parent_object_id = T.object_id INNER JOIN
sys.columns AS C ON FK.parent_object_id = C.object_id AND FK.parent_column_id = C.column_id

It always work like wonder, when done with Pure SQL.
Hope Script will be useful to play with Foreign Key in SQL Server.

Advertisements

About Deepak Mahto

Having 10 Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Helper SQL, SQL Server Learning and tagged , , , , , , , , , . Bookmark the permalink.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s