| 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