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

9 thoughts on “Table connection in GraphForm”

  1. Pingback: Crack
  2. I not to mention my guys appeared to be reviewing the nice items found on your site and then I got a horrible suspicion I had not expressed respect to the website owner for those strategies. All of the people were so warmed to study them and have in effect pretty much been enjoying those things. Appreciation for actually being really thoughtful and for having this form of good tips millions of individuals are really eager to discover. Our own sincere regret for not expressing appreciation to sooner.

  3. I am only commenting to make you know what a incredible experience my friend’s child encountered going through your site. She mastered a good number of details, with the inclusion of what it’s like to possess a great giving style to have the rest with no trouble know just exactly chosen specialized subject matter. You really did more than our own expected results. Thank you for showing these essential, healthy, educational and in addition easy tips on the topic to Emily.

  4. I wish to express some appreciation to the writer for bailing me out of this type of incident. Just after browsing through the the web and obtaining basics that were not powerful, I assumed my life was well over. Being alive without the answers to the issues you’ve solved as a result of this guide is a serious case, and those which might have badly affected my entire career if I hadn’t noticed your web site. Your main know-how and kindness in maneuvering every item was tremendous. I’m not sure what I would have done if I had not discovered such a solution like this. I can also at this point look ahead to my future. Thanks so much for this specialized and effective guide. I won’t hesitate to endorse your web site to any individual who ought to have care on this topic.

  5. I together with my buddies were found to be reviewing the good tips from your web blog and then unexpectedly got a terrible suspicion I never expressed respect to the website owner for those techniques. Most of the women happened to be as a result excited to read all of them and have in effect in reality been loving these things. Appreciate your actually being indeed helpful and then for using variety of decent subject matter millions of individuals are really eager to be informed on. My very own sincere apologies for not expressing appreciation to you sooner.

  6. Thank you for all of your effort on this site. Kate really likes making time for internet research and it’s easy to see why. We hear all relating to the dynamic ways you deliver important things on the website and as well foster participation from some others about this area of interest and our own child is truly studying a lot. Take advantage of the rest of the new year. You’re the one performing a remarkable job.

Leave a Reply

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