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...

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]
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