TSQL - Hierarchical table dependency graph based on foreign keys
Post date: Jun 23, 2011 12:47:26 PM
How to find out what table have some sort of relationship to another table? Recursively?
This snippet will output Tables that reference no tables (Level zero(0)), then those that reference level 0 (This is called level one(1)) and so on...
Based on a comment in this post.
Updated 2012-01-25: Can now handle tables that references themselves.
WITH base AS ( select tbl.name as [Table_Name] ,SCHEMA_NAME(tbl.schema_id) AS Table_Schema ,tbl2.name AS [ReferencedTable]from sys.tables as tbl left join sys.foreign_keys as cstr on cstr.parent_object_id=tbl.object_idleft join sys.foreign_key_columns as fk on fk.constraint_object_id=cstr.object_idleft join sys.columns as cfk on fk.parent_column_id = cfk.column_id and fk.parent_object_id = cfk.object_idleft join sys.columns as crk on fk.referenced_column_id = crk.column_id and fk.referenced_object_id = crk.object_idleft JOIN sys.tables AS tbl2 ON fk.referenced_object_id = tbl2.object_id ), more AS( SELECT Table_name , Table_Schema , [ReferencedTable] , 0 AS [level] FROM base WHERE [ReferencedTable] IS NULL UNION ALL SELECT b.Table_name , b.Table_Schema , b.[ReferencedTable] , m.[Level] + 1 AS [Level] FROM base b INNER JOIN more m ON b.[ReferencedTable] = m.Table_Name and b.ReferencedTable <> b.Table_Name -- Fix to handle tables that are referencing themselfs) SELECT DISTINCT * FROM more m WHERE NOT EXISTS(SELECT 1 FROM more m2 WHERE m2.level > m.level AND m2.table_name = m.table_name) ORDER BY m.[level] --OPTION (MAXRECURSION 32767);
Example output