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 :