SELECT	O1.xtype, O1.name, O2.xtype, O2.name, D.*
FROM	sys.sysdepends AS D
	JOIN sys.sysobjects AS O1
		 ON O1.id = D.id
	JOIN sys.sysobjects AS O2
		 ON O2.id = D.depid
		AND O2.xtype NOT IN ('U', 'V')	-- Ignore U=User table, V=View
ORDER BY O1.name, O2.name, D.number, D.depnumber, D.depid, D.id

; WITH CTE AS (
	SELECT
		o.[name] AS [Base Object]
		, o.[name] AS [Object]
		, o.[xtype] AS [Object Type]
		, o.[ID] AS [Object_ID]
		, CAST(o.[name] AS VARCHAR(MAX)) AS [Execution Path]
		, 0 AS [Level]
	FROM
		sys.sysObjects o
	WHERE
		o.xtype NOT IN ('U', 'V')

	UNION ALL SELECT
		CTE.[Base Object]
		, o.[name] AS [Object]
		, o.[xType] AS [Object Type]
		, o.[ID] AS [Object_ID]
		, CTE.[Execution Path] + CAST(' -> ' + o.[name] AS VARCHAR(MAX)) AS [Execution Path]
		, CTE.[level] + 1
	FROM
		CTE
		JOIN sys.sysDepends sd ON sd.[ID] = CTE.[object_ID]
		JOIN sys.sysObjects o ON o.[ID] = sd.[depID]
	WHERE
		o.xtype NOT IN ('U', 'V')
		AND CTE.[Execution Path] NOT LIKE '%' + o.[name] + '%'
	)
SELECT * FROM CTE 
ORDER BY
	CTE.[Base Object]
	, CTE.[Execution Path]

OPTION (MAXRECURSION 50);

Execution Path                                                       
---------------------------------------------------------------------
activateHolidayYearRollover                                          
activateHolidayYearRollover -> getProRatadAnnualHolidayEntitlement   
activateHolidayYearRollover -> getProRatadAnnualHolidayEntitlement ->
activateHolidayYearRollover -> getTimestamp                          
activateHolidayYearRollover -> holidayRound                          
activateHolidayYearRollover -> saveCarryOverHolidaysConfiguration    
addDefaultLifestyleEventTypeForCompanies                             
addEmployeeToTeam                                                    

/*** 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]
		, o.[name] AS [Object]
		, o.[xtype] AS [Object Type]
		, o.[ID] AS [Object_ID]
		, CAST(o.[name] AS VARCHAR(MAX)) AS [Execution Path]
		, 0 AS [Level]
	FROM
		sys.sysObjects o
	WHERE
		o.xtype NOT IN ('U', 'V')
		AND (o.[name] = @object OR @object IS NULL)
	UNION ALL SELECT
		ot.[Base Object]
		, o.[name] AS [Object]
		, o.[xType] AS [Object Type]
		, o.[ID] AS [Object_ID]
		, ot.[Execution Path] + CAST(' -> ' + o.[name] 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]
	, [Object]
	, [Object Type]
	, [Execution Path]
FROM
	ObjectTree 
ORDER BY
	[Base Object]
	, [Execution Path]

OPTION (MAXRECURSION 50);

quote:

---

Originally posted by Kristen

Nice job! I think I'll post my half-baked answers more often!!

Maybe ignore XTYPE = 'C' (Check), 'D' (Default) too?

---

SELECT DISTINCT
	[Base Object]
	, [Object]
	, [Object Type]
	, [Execution Path]
FROM	ObjectTree 
WHERE	1=1
--	AND ([Object Type] NOT IN ('TR', 'F') OR [Base Object] <> [Object])	-- Ignore specific self-referncing Triggers
	AND ([Base Object] <> [Object])			-- Ignore ALL self-referncing Triggers
 	AND [Object Type] NOT IN ('PK', 'UQ', 'SQ')	-- Usual ignore candidates
ORDER BY [Base Object]
	, [Execution Path]
OPTION (MAXRECURSION 50);

quote:

---

Originally posted by Kristen

(TC: Results on your first post are forcing wide-screen ... you may want to reformat them?)

---