-
Notifications
You must be signed in to change notification settings - Fork 0
/
GetFKDependencyForTable.sql
25 lines (24 loc) · 1.19 KB
/
GetFKDependencyForTable.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
DECLARE @table varchar(MAX) = ''
SELECT DISTINCT tables.schemaName FK_SCHEMA,
tables.name FK_TABLE_NAME,
fkc.name FK_COLUMN_NAME,
rpk.schemaname REFERENCED_SCHEMA,
rpk.table_name REFERENCED_TABLE_NAME,
rpk.name REFERENCED_COLUMN_NAME
FROM sys.foreign_keys
CROSS apply
(SELECT columns.name, referenced_column_id, referenced_object_id
FROM sys.foreign_key_columns
INNER JOIN sys.columns ON columns.column_id = foreign_key_columns.parent_column_id AND columns.[object_id] = foreign_key_columns.parent_object_id
WHERE foreign_key_columns.constraint_object_id = foreign_keys.[object_id]) fkc
CROSS apply
(SELECT schema_name(tables.schema_id) schemaname, object_name(tables.object_id) TABLE_NAME, columns.name
FROM sys.tables
INNER JOIN sys.columns ON tables.object_id = columns.object_id
AND columns.column_id = referenced_column_id
WHERE tables.object_id = fkc.referenced_object_id) rpk
CROSS apply
(SELECT schema_name(tables.schema_id) schemaname, name
FROM sys.tables
WHERE tables.object_id = foreign_keys.parent_object_id) tables
WHERE foreign_keys.parent_object_id = object_id(@table)