/*** (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)