Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_sql_server_foreign_keys [2019/01/27 11:29] (current)
Line 1: Line 1:
 +======  ======
 +
 +
 +
 +===== 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 :