Show pageOld revisionsBacklinksBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. CKG Edit ====== ====== ===== 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 ===== [[code_formattsql|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> ===== Version Information ===== * Entered : 02/27/2013 * Version :