SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Foreign Key Relationship Chart
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 04/29/2010 :  07:38:20  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
I wrote this to reverse engineer a third party schema that was playing up. I've seen a few people asking for similar information so here it is.

This report builds up a chart showing the cascading key relationships. So if you run it for table a and table b has a foreign key to A and table C has a foreign key to B (ad almost infinitum) then it will produce the relationships and show you the linkages.

The only parameter is @tableName which you can leave NULL if you want to work out the chart for everything.

The script hasn't been coded to deal with compound keys so user beware.

Enjoy

/*** (Key Cascade Report) *****************************************************
*
* Charlie (2010-April-20)
*
* Set @tableName to the name of the Root table you want to chart
* Dependancies for. leave NULL for all tables.
*
******************************************************************************/

DECLARE @tableName VARCHAR(255)        SET @tableName = NULL

/*****************************************************************************/

SET NOCOUNT ON

; WITH keyCas (
	[baseTable]
	, [tableName]
	, [tableId]
	, [keyname]
	, [Key Column]
	, [Target Column]
	, [level]
	, [tablePath]
	, [tableIdPath]
	)
AS (
	-- Anchor Definition
	SELECT
		tbl.[name]
		, tbl.[name]
		, tbl.[object_ID]
		, CAST('' AS VARCHAR(2000))
		, CAST('' AS VARCHAR(2000))
		, CAST('' AS VARCHAR(2000))
		, 0
		, CAST(tbl.[name] AS VARCHAR(MAX))
		, CAST(tbl.[object_ID] AS VARCHAR(MAX))
	FROM
		sys.objects tbl
	WHERE
		[type] = 'U'
		AND (
			[name] = @tableName
			OR
			@tableName IS NULL
			)

	-- Recursive Defintion
	UNION ALL SELECT
		kc.[baseTable]
		, tbl.[name]
		, tbl.[object_Id]
		, CAST(ky.[name] AS VARCHAR(2000))
		, CAST(QUOTENAME(icp.[name]) AS VARCHAR(2000))
		, CAST(QUOTENAME(kc.[tableName]) + '.' + QUOTENAME(icT.[name]) AS VARCHAR(2000))
		, kc.[level] + 1
		, kc.[tablePath] + CAST(' -> ' + tbl.[name] AS VARCHAR(MAX))
		, kc.[tableIdPath] + CAST(',' + CAST(tbl.[object_ID] AS VARCHAR(255)) AS VARCHAR(MAX))
	FROM
		sys.foreign_key_columns fkc
		JOIN sys.objects ky ON ky.[object_Id] = fkc.[constraint_object_id]
		JOIN sys.objects tbl ON tbl.[object_id] = fkc.[parent_object_Id]
		JOIN sys.objects target ON target.[object_ID] = fkc.[referenced_object_Id]

		JOIN sys.columns icp ON
			icp.[object_ID] = fkc.[parent_object_Id]
			AND icp.[column_Id] = fkc.[parent_column_Id]

		JOIN sys.columns icT ON
			icT.[object_ID] = fkc.[referenced_object_Id]
			AND icT.[column_Id] = fkc.[referenced_column_Id] 

		JOIN keyCas kc ON target.[object_ID] = kc.[tableId]
	WHERE
		kc.[tableIdPath] NOT LIKE '%' + CAST(tbl.[object_Id] AS VARCHAR(255)) + '%'
	)
SELECT
	[baseTable] AS [Root Table]
	, [tableName] AS [Leaf Table]
	, [keyname] AS [Key]
	, [Key Column]
	, [Target Column]
	, [tablePath] AS [Dependancy List]
FROM
	keyCas
ORDER BY
	[tablePath]

OPTION (MAXRECURSION 1000)



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Edited by - Transact Charlie on 04/29/2010 07:45:23

kevinwoodward
Starting Member

USA
2 Posts

Posted - 06/03/2010 :  08:52:39  Show Profile  Reply with Quote
This does not work.
Will you be fixing it as I need something that provides what you describe.

Kevin
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/03/2010 :  08:58:39  Show Profile  Reply with Quote
Telling TC:

what doesn't work / what error you get / what data you tried + expected + actually got

would help.

Otherwise where would you expect him to start looking for your needle in your haystack?
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 06/03/2010 :  09:30:58  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Hi kevinwoodward,

As Kristen mentioned already....

How would I possibly be able to "fix" this script for you based on the information you provided.

Post the error message.

The script uses a CTE so you need to be on at least compatibility level 90 (2005) or above.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000