Table connection in GraphForm

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

2 thoughts on “Table connection in GraphForm”

  1. Pingback: Crack

Leave a Reply

Your email address will not be published. Required fields are marked *