This query will list all of the foreign keys for a given table in a SQL Server database.

The @TableName variable is used to set the table name to list the foreign keys.

The foreign keys where the given table is the parent table will be sorted at the top of the list, sorted by the referenced table's name. All of the tables where the given table is the reference table will follow sorted by the parent table's name.

None. This is a selection query and no data is modified in the running of it.

code format="tsql"

DECLARE @TableName VARCHAR(100);

SET @TableName = 'OrderHeader';

DECLARE @T TABLE(ForeignKeyName VARCHAR(255), ParentTableName VARCHAR(255), ReferencedTableName VARCHAR(255),

               ColumnID INT, ParentColumnName VARCHAR(255), ReferencedColumnName VARCHAR(255));

INSERT INTO @T

SELECT fk.name ForeignKeyName, pt.name ParentTableName, rt.name ReferencedTableName,

       fkc.constraint_column_id ColumnID, pc.name ParentColumnName, rc.name ReferencedColumnName

FROM sys.foreign_key_columns fkc

       JOIN sys.all_objects fk ON fk.object_id = fkc.constraint_object_id
       JOIN sys.all_objects pt ON fkc.parent_object_id = pt.object_id
       JOIN sys.all_columns pc ON fkc.parent_object_id = pc.object_id AND fkc.parent_column_id = pc.column_id
       JOIN sys.all_objects rt ON fkc.referenced_object_id = rt.object_id
       JOIN sys.all_columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id

WHERE pt.name = @TableName OR rt.name = @TableName

SELECT ForeignKeyName, ParentTableName, ReferencedTableName,

     STUFF( (SELECT   ', ' + T2.ParentColumnName
             FROM     @T T2
             WHERE    T.ForeignKeyName = T2.ForeignKeyName
             ORDER BY T2.ColumnID
             FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '') AS ParentColumnNames,
     STUFF( (SELECT   ', ' + T2.ReferencedColumnName
             FROM     @T T2
             WHERE    T.ForeignKeyName = T2.ForeignKeyName
             ORDER BY T2.ColumnID
             FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '') AS ReferencedColumnNames

FROM @T T

GROUP BY ForeignKeyName, ParentTableName, ReferencedTableName

ORDER BY CASE WHEN ParentTableName = @TableName THEN '' ELSE ParentTableName END, ReferencedTableName, ForeignKeyName

<code>

  • Entered : 02/27/2013
  • Version :
You could leave a comment if you were logged in.