If you ever worked around legacy databases, specially a one with more than a couple hundred tables, you might need some information about the core tables. I created a simple query, that will create the nodes of a Graph around the specified CoreTable.
Node levels:
…
-3 => Referenced in a -2 level table
-2 => Referenced in a -1 level table
-1 => Referenced in Core table
0 => Core table
1 => Referencing to Core table
2 => Referencing to a 1 level table
3 => Referencing to a 2 level table
…
DECLARE @coretable varchar(200) = 'CoreTable'; CREATE TABLE #TempDepthGraph ( NodeLevel int, NodeTable varchar(200), NodeColumn varchar(200), RefrencedTable varchar(200), RefrencedColumn varchar(200), ID varchar(200) ); INSERT INTO #TempDepthGraph (NodeLevel, NodeTable) VALUES (0,@coretable); DECLARE @levelstep int = 1; DECLARE @toanalyze int = 1; WHILE (@toanalyze > 0) BEGIN INSERT INTO #TempDepthGraph (ID, NodeLevel, NodeTable, NodeColumn, RefrencedTable, RefrencedColumn) SELECT obj.name, @levelstep, tab1.name, col1.name, tab2.name, col2.name FROM sys.foreign_key_columns fkc INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id WHERE NOT EXISTS (SELECT 1 FROM #TempDepthGraph WHERE ID = obj.name) AND EXISTS (SELECT 1 FROM #TempDepthGraph WHERE NodeTable = tab2.name AND NodeLevel >= 0) SET @toanalyze = ( SELECT COUNT(1) FROM sys.foreign_key_columns fkc INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id WHERE NOT EXISTS (SELECT 1 FROM #TempDepthGraph WHERE ID = obj.name) AND EXISTS (SELECT 1 FROM #TempDepthGraph WHERE NodeTable = tab2.name AND NodeLevel >= 0) ) SET @levelstep = @levelstep+1; END SET @levelstep = -1; SET @toanalyze = 1; WHILE (@toanalyze > 0) BEGIN INSERT INTO #TempDepthGraph (ID, NodeLevel, NodeTable, NodeColumn, RefrencedTable, RefrencedColumn) SELECT obj.name, @levelstep, tab2.name, col2.name, tab1.name, col1.name FROM sys.foreign_key_columns fkc INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id WHERE NOT EXISTS (SELECT 1 FROM #TempDepthGraph WHERE ID = obj.name) AND EXISTS (SELECT 1 FROM #TempDepthGraph WHERE NodeTable = tab1.name AND NodeLevel <= 0) SET @toanalyze = ( SELECT COUNT(1) FROM sys.foreign_key_columns fkc INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id WHERE NOT EXISTS (SELECT 1 FROM #TempDepthGraph WHERE ID = obj.name) AND EXISTS (SELECT 1 FROM #TempDepthGraph WHERE NodeTable = tab1.name AND NodeLevel <= 0) ) SET @levelstep = @levelstep-1; END SELECT * FROM #TempDepthGraph ORDER BY NodeLevel ASC DROP TABLE #TempDepthGraph