SQL Server Foreign Key Constraints – sp_fkeys on ‘Roids!

This query gives you enough information to recreate a foreign key. Enjoy!

--===========================================
--Returns what you need to recreate foreign
-- keys. SP_FKEYS on steriods...
--===========================================

SELECT
SCHEMA_NAME(fk.schema_id) + '.' + OBJECT_NAME(fk.parent_object_id) as [Primary Table Name],
fk.name as [Foreign Key Name],
OBJECT_NAME(fk.referenced_object_id) as [Foriegn Key Table Name],
fkcol.name as [Foriegn Key Column Name],
pkcol.name as [Primary Table Column Name]
FROM
sys.foreign_keys fk
INNER JOIN sys.columns fkcol
ON fkcol.object_id = fk.referenced_object_id AND fkcol.column_id = fk.key_index_id
INNER JOIN sys.foreign_key_columns pk
ON pk.constraint_object_id = fk.object_id
INNER JOIN sys.columns pkcol
ON pkcol.object_id = pk.parent_object_id AND pkcol.column_id = parent_column_id
WHERE OBJECT_ID('insert table name here') = fk.parent_object_id

 

Advertisements

2 thoughts on “SQL Server Foreign Key Constraints – sp_fkeys on ‘Roids!

Have an opinion? Leave an opinion!

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