Key Discovery III

Categories: News, Professional, Scripts, SSC
Comments: 2 Comments
Published on: February 1, 2010

In Part I and Part II of the series, I discussed documenting and discovering Primary Keys and Clustered Indexes.  In this article I will show how to document table relationships from a hierarchical standpoint.  The type of documentation I will demonstrate in this article will prove insightful and useful – if for nothing more than pure documentation.

I have had the pleasure of sharing this information in a User Group meeting.  That was the first time I had presented at a User’s Group meeting.  I gave that presentation shortly after hammering out the script to help document a database as a part of a project that is still on-going.  For the project, I first set out to find a script that fulfill the requirements for me.  All I could find were scripts similar to what I had already written.  They would present the Tables and foreign keys for those tables as well as the child tables.  The problem with this was that the list was in no particular order.  I then found another script that presented the data in an hierarchy – however it was not a true hierarchy.  I needed a script that could lay out the hierarchy of the foreign keys in the database when provided with one table from the database as the starting point.  This is how I define the perspective.  Now would be a good time to discuss the requirements I had defined for this script.

  1. Document Foreign Key Relationships
  2. Foreign Key Relationships listed in Hierarchical Format
  3. Create Perspectives (View of Hierarchy Tree from Specified Parent Table)
  4. No Cursors or While Loops

Why are these requirements necessary?  This is to help document a database.  I needed to be able to see the ERD at a reasonable size.  By adding the perspective requirement I could take a large database and reduce the number of objects displayed very quickly.  Furthermore, by using a script, I can create the documentation that displays the table relationships very quickly.  This information also helps in knowing the insert and delete order of data in the respective tables as they relate to one another.

The scripts I could find on the net were quickly eliminated due to the requirements.  Most used a looping mechanism, while others did not create a true hierarchy.  Thus I turned to my own information store to develop a script that could traverse the system views and create the kind of report that I needed.

To meet the requirements, I started looking to a CTE.  And yes a recursive CTE at that.  I know – not 100% set-based due to the recursion – but, it is very efficient for this purpose.  In scenarios such as this, this kind of solution is acceptable.  So, starting with the base query:

;With FkCascade AS (
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,sc2.name AS ParentColumn
	,0 AS FKLevel
	FROM sys.foreign_key_columns sfkc
		INNER JOIN sys.foreign_keys sfk
			ON sfkc.constraint_object_id = sfk.object_id
		INNER JOIN sys.COLUMNS sc
			ON sfkc.parent_object_id = sc.object_id
			AND sfkc.parent_column_id = sc.column_id
		INNER JOIN sys.COLUMNS sc2
			ON sfkc.referenced_object_id = sc2.object_id
			AND sfkc.referenced_column_id = sc2.column_id
	UNION ALL
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,sc2.name AS ParentColumn, FKLevel + 1
		FROM FkCascade F
		INNER JOIN sys.foreign_keys sfk
			ON f.ChildTable = OBJECT_NAME(sfk.referenced_object_id)
		INNER JOIN sys.foreign_key_columns sfkc
			ON sfkc.constraint_object_id = sfk.object_id
		INNER JOIN sys.COLUMNS sc
			ON sfkc.parent_object_id = sc.object_id
			AND sfkc.parent_column_id = sc.column_id
		INNER JOIN sys.COLUMNS sc2
			ON sfkc.referenced_object_id = sc2.object_id
			AND sfkc.referenced_column_id = sc2.column_id
)
 
SELECT * FROM FKCascade
	ORDER BY ParentTable ASC
	OPTION (maxrecursion 500)

This query gets me pretty close to the desired outcome and only needs a few more tweaks.  The above script more or less does the same sort of thing I saw other scripts doing that I found from internet searches.  The tables and foreign key level are displayed, but no linkage is quickly displayed by the query.  That can be resolved by adding a varbinary to the the query and then concatenating the value with each recursion.

;With FkCascade (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol) AS (
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,sc2.name AS ParentColumn
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,0 AS FKLevel,cast(row_number() over(ORDER BY sfk.referenced_object_id) AS varbinary(50)) --varbinary value and sortcol help to create the hierarchy.  Larger trees require a larger varbinary value
	FROM sys.foreign_key_columns sfkc
		INNER JOIN sys.foreign_keys sfk
			ON sfkc.constraint_object_id = sfk.object_id
		INNER JOIN sys.COLUMNS sc
			ON sfkc.parent_object_id = sc.object_id
			AND sfkc.parent_column_id = sc.column_id
		INNER JOIN sys.COLUMNS sc2
			ON sfkc.referenced_object_id = sc2.object_id
			AND sfkc.referenced_column_id = sc2.column_id
	UNION ALL
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,sc2.name AS ParentColumn
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,FKLevel + 1,cast(SortCol + cast(row_number() over(ORDER BY sfk.referenced_object_id) AS varbinary(3)) AS varbinary(50))
		FROM FkCascade F
		INNER JOIN sys.foreign_keys sfk
			ON f.ChildTable = OBJECT_NAME(sfk.referenced_object_id)
		INNER JOIN sys.foreign_key_columns sfkc
			ON sfkc.constraint_object_id = sfk.object_id
		INNER JOIN sys.COLUMNS sc
			ON sfkc.parent_object_id = sc.object_id
			AND sfkc.parent_column_id = sc.column_id
		INNER JOIN sys.COLUMNS sc2
			ON sfkc.referenced_object_id = sc2.object_id
			AND sfkc.referenced_column_id = sc2.column_id
)
 
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol,LEN(sortcol)
FROM FKCascade
	ORDER BY SortCol ASC
	OPTION (maxrecursion 500)

Ok, now the query is very close to what I need.  From the requirements, I only need to alter the script just a bit more in order to be able to create a perspective.  The perspective is achieved through variables and a slight change to the CTE.

DECLARE	@StartParentTable	Varchar(256)
 
SELECT @StartParentTable = 'MyTable'
 
;With FkCascade (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol) AS (
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,sc2.name AS ParentColumn
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,0 AS FKLevel,cast(row_number() over(ORDER BY sfk.referenced_object_id) AS varbinary(50))
	FROM sys.foreign_key_columns sfkc
		INNER JOIN sys.foreign_keys sfk
			ON sfkc.constraint_object_id = sfk.object_id
		INNER JOIN sys.COLUMNS sc
			ON sfkc.parent_object_id = sc.object_id
			AND sfkc.parent_column_id = sc.column_id
		INNER JOIN sys.COLUMNS sc2
			ON sfkc.referenced_object_id = sc2.object_id
			AND sfkc.referenced_column_id = sc2.column_id
		WHERE OBJECT_NAME(sfk.referenced_object_id) = @StartParentTable
	UNION ALL
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,sc2.name AS ParentColumn
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,FKLevel + 1,cast(SortCol + cast(row_number() over(ORDER BY sfk.referenced_object_id) AS varbinary(3)) AS varbinary(50))
		FROM FkCascade F
		INNER JOIN sys.foreign_keys sfk
			ON f.ChildTable = OBJECT_NAME(sfk.referenced_object_id)
		INNER JOIN sys.foreign_key_columns sfkc
			ON sfkc.constraint_object_id = sfk.object_id
		INNER JOIN sys.COLUMNS sc
			ON sfkc.parent_object_id = sc.object_id
			AND sfkc.parent_column_id = sc.column_id
		INNER JOIN sys.COLUMNS sc2
			ON sfkc.referenced_object_id = sc2.object_id
			AND sfkc.referenced_column_id = sc2.column_id
)
 
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol
FROM FKCascade
	ORDER BY SortCol ASC
	OPTION (maxrecursion 500)
Go

Through the above script, I am now able to achieve all of the requirements that had been pre-defined.  In testing the query, however, I discovered that the query was not yet quite complete.  There comes a time when there is an additional foreign key defined on the table that does not fit into the above query as a downstream perspective of the origin table.  There may be an occasion where an additional “parent” table needs to be displayed in the query.  Thus I must be able to traverse back upwards now in order to complete the perspective.  This was achieved through the following query.

DECLARE	@StartParentTable	Varchar(256)
 
SELECT @StartParentTable = 'MyTable'
 
;With FkCascade (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol) AS (
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,sc2.name AS ParentColumn
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,0 AS FKLevel,cast(row_number() over(ORDER BY sfk.referenced_object_id) AS varbinary(50))
	FROM sys.foreign_key_columns sfkc
		INNER JOIN sys.foreign_keys sfk
			ON sfkc.constraint_object_id = sfk.object_id
		INNER JOIN sys.COLUMNS sc
			ON sfkc.parent_object_id = sc.object_id
			AND sfkc.parent_column_id = sc.column_id
		INNER JOIN sys.COLUMNS sc2
			ON sfkc.referenced_object_id = sc2.object_id
			AND sfkc.referenced_column_id = sc2.column_id
		WHERE OBJECT_NAME(sfk.referenced_object_id) = @StartParentTable
	UNION ALL
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,sc2.name AS ParentColumn
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,FKLevel + 1,cast(SortCol + cast(row_number() over(ORDER BY sfk.referenced_object_id) AS varbinary(3)) AS varbinary(50))
		FROM FkCascade F
		INNER JOIN sys.foreign_keys sfk
			ON f.ChildTable = OBJECT_NAME(sfk.referenced_object_id)
		INNER JOIN sys.foreign_key_columns sfkc
			ON sfkc.constraint_object_id = sfk.object_id
		INNER JOIN sys.COLUMNS sc
			ON sfkc.parent_object_id = sc.object_id
			AND sfkc.parent_column_id = sc.column_id
		INNER JOIN sys.COLUMNS sc2
			ON sfkc.referenced_object_id = sc2.object_id
			AND sfkc.referenced_column_id = sc2.column_id
), WalkBackUp (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol) AS (
	SELECT DISTINCT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,sc2.name AS ParentColumn
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,-1 AS FKLevel,cast(SortCol + cast(row_number() over(ORDER BY sfk.referenced_object_id) AS varbinary(3)) AS varbinary(50))
		FROM FkCascade F
			INNER JOIN sys.foreign_keys sfk
				ON f.ChildTable = OBJECT_NAME(sfk.parent_object_id)
				AND F.ParentTable <> OBJECT_NAME(sfk.referenced_object_id)
			INNER JOIN sys.foreign_key_columns sfkc
				ON sfkc.constraint_object_id = sfk.object_id
			INNER JOIN sys.COLUMNS sc
				ON sfkc.parent_object_id = sc.object_id
				AND sfkc.parent_column_id = sc.column_id
			INNER JOIN sys.COLUMNS sc2
				ON sfkc.referenced_object_id = sc2.object_id
				AND sfkc.referenced_column_id = sc2.column_id
	UNION ALL
	SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
	,sc2.name AS ParentColumn
	,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
	,sc.name AS ChildColumn
	,f.FKLevel -1,cast(SortCol + cast(row_number() over(ORDER BY sfk.referenced_object_id) AS varbinary(3)) AS varbinary(50))
		FROM WalkBackup F
			INNER JOIN sys.foreign_keys sfk
				ON f.parentTable = OBJECT_NAME(sfk.parent_object_id)
			INNER JOIN sys.foreign_key_columns sfkc
				ON sfkc.constraint_object_id = sfk.object_id
			INNER JOIN sys.COLUMNS sc
				ON sfkc.parent_object_id = sc.object_id
				AND sfkc.parent_column_id = sc.column_id
			INNER JOIN sys.COLUMNS sc2
				ON sfkc.referenced_object_id = sc2.object_id
				AND sfkc.referenced_column_id = sc2.column_id
		WHERE f.parentTable NOT IN (SELECT ParentTable FROM FKCascade)
)
 
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol
FROM FKCascade
UNION ALL
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, SortCol
FROM WalkBackUp
	ORDER BY SortCol ASC
	OPTION (maxrecursion 500)

This query can easily be modified to pull back just the unique table names that are found in the CTE.  Why would that be necesary?  That would provide an easy method to pull back unique tables in the event that an ERD is to be derived from the perspective.  I didn’t delve too much into any of the scripts presented here.  At this point in the series, the only complexity that may need to be explained is the recursive piece of the scripts.  I think there are plenty of articles on that very subject.  The main goal here is to show that the documentation of FKs can be made relatively simple as well as provide plenty of insight into the database.  Test the queries.

I am including the scripts and the execution plan for the final script.  Included in the scripts will be the presentation given on the same subject.  The inclusion of that slide deck is a secondary reason for not getting into great detail in this post.  You can download it here.

The final blog in this particular series will be very short. I will cover the need for indexes on FKs. This is another topic that has been recently discussed. I will explore a couple of different scripts and compare and contrast those scripts performance.

2 Comments - Leave a comment
  1. [...] to bring it up and use it again.  Without further adieu, you can read about that script and CTE here.  I hope you enjoy. Related Posts:Having Cake and Eating it tooPrimary Key Discovery IISQL [...]

  2. […] Today I would like to revisit a post of mine that is rather old.  More precisely, the script in that post needs revisiting.  This is one of my more favorite scripts and I still have more ideas to implement with it.  The post/script in question can be found here. […]

Leave a comment

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>






Calendar
February 2010
M T W T F S S
« Jan   Mar »
1234567
891011121314
15161718192021
22232425262728
Content
SQLHelp

SQLHelp


Welcome , today is Friday, April 25, 2014