/*** Generate Procedure and Function Dependancy Chart *************************
*
* Makes a tree list of object(s) dependancies. Set @object to the desired
* object name or leave NULL to bring back all objects.
*
* Charlie (2010-04-15)
*
******************************************************************************/

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

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

; WITH ObjectTree AS (
	SELECT
		o.[name] AS [Base Object]
		, CAST('' AS SYSNAME) AS [Dependant Object]
		, o.[xtype] AS [Object Type]
		, o.[ID] AS [Object_ID]
		, CAST('' AS VARCHAR(MAX)) AS [Execution Path]
		, 0 AS [Level]
	FROM
		sys.sysObjects o
	WHERE
		o.xtype IN ('P', 'FN')
		AND (o.[name] = @object OR @object IS NULL)
	UNION ALL SELECT
		ot.[Base Object]
		, o.[name] AS [Dependant Object]
		, o.[xType] AS [Object Type]
		, o.[ID] AS [Object_ID]
		, ot.[Execution Path] + CAST(
			CASE ot.[Execution Path] WHEN '' THEN o.[name]
			ELSE ' -> ' + o.[name]
		END AS VARCHAR(MAX)) AS [Execution Path]
		, ot.[level] + 1
	FROM
		ObjectTree ot
		JOIN sys.sysDepends sd ON sd.[ID] = ot.[object_ID]
		JOIN sys.sysObjects o ON o.[ID] = sd.[depID]
	WHERE
		o.xtype NOT IN ('U', 'V')
		AND ot.[Execution Path] NOT LIKE '%' + o.[name] + '%'
	)

SELECT
	[Base Object]
	, [Dependant Object]
	, [Object Type]
	, [Execution Path]
FROM
	ObjectTree 
ORDER BY
	[Base Object]
	, [Execution Path]

OPTION (MAXRECURSION 50);

WHERE [Base Object] != [Object]

AND ot.[Execution Path] NOT LIKE '%' + o.[name] + '%'