/*** (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 = 'Employee'

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

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)

quote:

---

Originally posted by maifs

when i am trying with script then it throws some errors like:

Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'WITH'.
Server: Msg 170, Level 15, State 1, Line 28
Line 28: Incorrect syntax near 'MAX'.
Server: Msg 170, Level 15, State 1, Line 49
Line 49: Incorrect syntax near 'MAX'.
Server: Msg 170, Level 15, State 1, Line 81
Line 81: Incorrect syntax near 'MAXRECURSION'.

---

quote:

---

Originally posted by maifs

version is this:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
but i am using sql server 2005.
if this is ms server 2000 then how can i convert it into 2005 without any loss of data?

---