Explanation of SQL
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.
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
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>
Version Information
- Entered : 02/27/2013
- Version :