Mit diesem MS SQL-Script ist es möglich zu einer gegebenen Tabelle alle Tabellen zu finden, von denen diese durch
Foreign Key Constraints abhängig ist.
declare @TableName nvarchar(50)='TestTable2'
declare @SchemaName nvarchar(10)='dbo'
select schRef.name+'.'+referenced.name from sys.foreign_keys fk
join sys.tables referenced on referenced.object_id = fk.referenced_object_id
join sys.tables base on base.object_id = fk.parent_object_id
join sys.schemas schBase on schBase.schema_id = base.schema_id
join sys.schemas schRef on schRef.schema_id = referenced.schema_id
where base.name=@TableName and schBase.name=@SchemaName
um das ganze jetzt auch noch vollständig ausgeben zu können (inkl. Column), wäre folgendes der Code:
declare @TableName nvarchar(50)='TestTable2'
declare @SchemaName nvarchar(10)='dbo'
select
schRef.name+'.'+referenced.name ReferencedTable
, colRef.name ReferencedColumn
, schBase.name+'.'+base.name BaseTable
,colBase.name BaseColumn
from sys.foreign_keys fk
-- ref table
join sys.tables referenced on referenced.object_id = fk.referenced_object_id
join sys.schemas schRef on schRef.schema_id = referenced.schema_id
-- base table
join sys.tables base on base.object_id = fk.parent_object_id
join sys.schemas schBase on schBase.schema_id = base.schema_id
-- columns
join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id
join sys.all_columns colBase on colBase.column_id = fkc.parent_column_id and colBase.object_id = base.object_id
join sys.all_columns colRef on colRef.column_id = fkc.referenced_column_id and colRef.object_id = referenced.object_id
where base.name=@TableName and schBase.name=@SchemaName
