DECLARE @tableName VARCHAR(64); | |
SET @tableName = 'MY_TABLE_NAME'; | |
SELECT | |
SO_P.name AS parent_table | |
, SC_P.name AS parent_column | |
, 'is a foreign key of' AS direction | |
, SO_R.name AS referenced_table | |
, SC_R.name AS referenced_column | |
, * | |
FROM | |
sys.foreign_key_columns FKC | |
INNER JOIN sys.objects SO_P ON SO_P.object_id = FKC.parent_object_id | |
INNER JOIN sys.columns SC_P ON (SC_P.object_id = FKC.parent_object_id) AND (SC_P.column_id = FKC.parent_column_id) | |
INNER JOIN sys.objects SO_R ON SO_R.object_id = FKC.referenced_object_id | |
INNER JOIN sys.columns SC_R ON (SC_R.object_id = FKC.referenced_object_id) AND (SC_R.column_id = FKC.referenced_column_id) | |
WHERE | |
((SO_P.name = @tableName) AND (SO_P.type = 'U')) | |
OR | |
((UPPER(SO_R.name) = UPPER(@tableName)) AND (SO_R.type = 'U')) | |
; |
26/Oct/2020
Leave a Reply