SQL Code Snippet: Anzeigen aller durch Fremdschlüssel abhängigen Tabellen

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