Differences
This shows you the differences between two versions of the page.
— |
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' | ||
+ | |||
+ | |||
+ | |||
+ | ===== Risk of Data Corruption if Run Improperly ===== | ||
+ | |||
+ | **None**. | ||
+ | |||
+ | |||
+ | |||
+ | ===== SQL ===== | ||
+ | |||
+ | |||
+ | |||
+ | [[code_formattsql|code format=" | ||
+ | |||
+ | |||
+ | |||
+ | DECLARE @TableName VARCHAR(100); | ||
+ | |||
+ | |||
+ | |||
+ | SET @TableName = ' | ||
+ | |||
+ | |||
+ | |||
+ | DECLARE @T TABLE(ForeignKeyName VARCHAR(255), | ||
+ | |||
+ | |||
+ | |||
+ | | ||
+ | |||
+ | |||
+ | |||
+ | INSERT INTO @T | ||
+ | |||
+ | |||
+ | |||
+ | SELECT | ||
+ | |||
+ | |||
+ | |||
+ | | ||
+ | |||
+ | |||
+ | |||
+ | FROM | ||
+ | |||
+ | |||
+ | |||
+ | 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, | ||
+ | |||
+ | |||
+ | |||
+ | | ||
+ | |||
+ | |||
+ | |||
+ | | ||
+ | |||
+ | |||
+ | |||
+ | | ||
+ | |||
+ | |||
+ | |||
+ | ORDER BY T2.ColumnID | ||
+ | |||
+ | |||
+ | |||
+ | FOR XML PATH('' | ||
+ | |||
+ | |||
+ | |||
+ | | ||
+ | |||
+ | |||
+ | |||
+ | | ||
+ | |||
+ | |||
+ | |||
+ | | ||
+ | |||
+ | |||
+ | |||
+ | ORDER BY T2.ColumnID | ||
+ | |||
+ | |||
+ | |||
+ | FOR XML PATH('' | ||
+ | |||
+ | |||
+ | |||
+ | FROM @T T | ||
+ | |||
+ | |||
+ | |||
+ | GROUP BY ForeignKeyName, | ||
+ | |||
+ | |||
+ | |||
+ | ORDER BY CASE WHEN ParentTableName = @TableName THEN '' | ||
+ | |||
+ | |||
+ | |||
+ | < | ||
+ | ===== Version Information ===== | ||
+ | * Entered : 02/27/2013 | ||
+ | * Version : | ||