Monday, 28 October 2013

Getting List of all the foreign keys and there details with table and field name

SELECT tabs.name AS [Reference Table],
   (SELECT name FROM sys.tables AS ctab
       WHERE (object_id = fk.parent_object_id)) AS [Parent Table],
       fk.name AS [Key Name],
        ac.name AS [Column Name]
FROM sys.tables AS tabs INNER JOIN            
sys.foreign_keys AS fk ON fk.referenced_object_id = tabs.object_id inner join            
sys.foreign_key_columns as fkc on fk.object_id = fkc.constraint_object_id inner join            
sys.all_columns as ac on ac.column_id = fkc.parent_column_id
             and ac.object_id = fkc.parent_object_id
ORDER BY tabs.name, 2

No comments:

Post a Comment