--exec sp_GetObjectDependencies @IncludeConstraints = 0
 
CREATE PROCEDURE sp_GetObjectDependencies
(
	@ObjectToCheck		VARCHAR(100) = '',
	@IncludeConstraints	bit = 0,
	@Help				bit = 0
)
 
AS
 
IF @Help = 1
BEGIN
    SELECT N'@Help' AS [Parameter Name] ,
           N'BIT' AS [DATA TYPE] ,
           N'Displays this help message.' AS [Parameter Description]
 
    UNION ALL
    SELECT N'@ObjectToCheck',
           N'VARCHAR(100)',
           N'Specify a specific object of which to check the dependancies of. Default is to check all objects.'
 
    UNION ALL
    SELECT N'@IncludeConstraints',
           N'BIT',
           N'Specify whether you want to include contraints and keys for the objects being checked for it''s dependancies. Default is to exclude constraints'
END
ELSE
BEGIN
	DECLARE @Objects AS TABLE
	(
		ID			INT	IDENTITY(1,1),
		SchemaName	VARCHAR(100),
		ObjectName	VARCHAR(100)
	)
 
	DECLARE @Dependancies AS TABLE
	(
		SourceObject						VARCHAR(200),
		SourceObjectType					VARCHAR(200),
		SourceObjectLastExecutionDate		datetime,
		SourceObjectCreateDate				datetime,
		SourceObjectModifiedDate			datetime,
		SourceObjectIsMSShipped				bit,
		ReferencingObject					VARCHAR(200),
		ReferencingObjectType				VARCHAR(200),
		ReferencingObjectLastExecutionDate	datetime,
		ReferencingObjectCreateDate			datetime,
		ReferencingObjectModifiedDate		datetime,
		ReferencingObjectIsMSShipped		bit,
		IsReferencingObjectDependant		bit
	)
 
	DECLARE @Constraints AS TABLE
	(
		SourceObject						VARCHAR(200),
		SourceObjectType					VARCHAR(200),
		SourceObjectLastExecutionDate		datetime,
		SourceObjectCreateDate				datetime,
		SourceObjectModifiedDate			datetime,
		SourceObjectIsMSShipped				bit,
		ReferencingObject					VARCHAR(200),
		ReferencingObjectType				VARCHAR(200),
		ReferencingObjectLastExecutionDate	datetime,
		ReferencingObjectCreateDate			datetime,
		ReferencingObjectModifiedDate		datetime,
		ReferencingObjectIsMSShipped		bit,
		IsReferencingObjectDependant		bit
	)
 
	INSERT INTO @Objects(SchemaName, ObjectName)
	SELECT
		s.name AS SchemaName,
		o.name AS ObjectName
	FROM
		sys.objects o
	INNER JOIN
		sys.schemas s
	ON
		s.schema_id = o.schema_id
	WHERE
		o.name = (CASE WHEN @ObjectToCheck = '' THEN o.name ELSE @ObjectToCheck END)
 
	DECLARE @CurrentRow		INT
	DECLARE @MaxRow			INT
 
	SET @CurrentRow = (SELECT MIN(ID) FROM @Objects)
	SET @MaxRow = (SELECT MAX(ID) FROM @Objects)
 
	while @CurrentRow <= @MaxRow
	BEGIN
		DECLARE @CurrentObjectName		VARCHAR(100)
		DECLARE @CurrentObjectSchema	VARCHAR(100)
		DECLARE @CurrentObject			VARCHAR(200)
		DECLARE @CurrentObjectID		BIGINT
 
		SET @CurrentObjectName = (SELECT ObjectName FROM @Objects WHERE ID = @CurrentRow)
		SET @CurrentObjectSchema = (SELECT SchemaName FROM @Objects WHERE ID = @CurrentRow)
		SET @CurrentObject = (SELECT SchemaName + '.' + ObjectName FROM @Objects WHERE ID = @CurrentRow)
		SET @CurrentObjectID = (SELECT object_id FROM sys.objects o
								INNER JOIN sys.schemas s ON s.schema_id = o.schema_id 
								WHERE o.name = @CurrentObjectName AND s.name = @CurrentObjectSchema)
 
		INSERT INTO @Dependancies
		SELECT
			@CurrentObject AS SourceObject,
			so.type_desc AS SourceObjectType,
			ss.last_execution_time AS SourceObjectLastExecutionDate,
			so.create_date AS SourceObjectCreateDate,
			so.modify_date AS SourceObjectModifiedDate,
			so.is_ms_shipped AS SourceObjectIsMSShipped,
			re.referencing_schema_name + '.' + re.referencing_entity_name AS ReferencingObject,
			ro.type_desc AS ReferencingObjectType,
			rs.last_execution_time AS ReferencingObjectLastExecutionDate,
			ro.create_date AS ReferencingObjectCreateDate,
			ro.modify_date AS ReferencingObjectModifiedDate,
			ro.is_ms_shipped AS ReferencingObjectIsMSShipped,
			re.is_caller_dependent AS IsReferencingObjectDependant
 
		FROM 
			sys.dm_sql_referencing_entities (@CurrentObject, 'OBJECT') re
		LEFT JOIN
			sys.objects so
		ON
			so.object_id = @CurrentObjectID
		LEFT JOIN
			sys.objects ro
		ON
			ro.object_id = re.referencing_id
		LEFT JOIN 
			sys.dm_exec_procedure_stats ss 
		ON 
			ss.object_id = so.object_id
		LEFT JOIN 
			sys.dm_exec_procedure_stats rs 
		ON 
			rs.object_id = ro.object_id
 
		INSERT INTO @Constraints
		SELECT DISTINCT
			@CurrentObject AS SourceObject,
			so.type_desc AS SourceObjectType,
			NULL AS SourceObjectLastExecutionDate,
			so.create_date AS SourceObjectCreateDate,
			so.modify_date AS SourceObjectModifiedDate,
			so.is_ms_shipped AS SourceObjectIsMSShipped,
			rs.name + '.' + ro.name AS ReferencingObject,
			ro.type_desc AS ReferencingObjectType,
			NULL AS ReferencingObjectLastExecutionDate,
			ro.create_date AS ReferencingObjectCreateDate,
			ro.modify_date AS ReferencingObjectModifiedDate,
			ro.is_ms_shipped AS ReferencingObjectIsMSShipped,
			1 AS IsReferencingObjectDependant
		FROM 
			sys.objects so
		LEFT JOIN
			sys.objects ro
		ON
			ro.parent_object_id = so.object_id
		LEFT JOIN
			sys.schemas rs 
		ON
			rs.schema_id = ro.schema_id
		WHERE
			so.object_id = @CurrentObjectID
		AND
			so.type_desc <> 'INTERNAL_TABLE'
		AND
			ro.type IN ('C','D','F','PK','UQ')
 
		SET @CurrentRow = @CurrentRow + 1
	END
 
	IF(@IncludeConstraints = 0)
	BEGIN
		SELECT DISTINCT * FROM @Dependancies
		WHERE ReferencingObjectType NOT LIKE '%CONSTRAINT%'
		ORDER BY SourceObject, ReferencingObject
	END
	ELSE
	BEGIN
		SELECT DISTINCT * FROM @Dependancies
		UNION
		SELECT DISTINCT * FROM @Constraints
		ORDER BY SourceObject, ReferencingObject
	END
END
 
/*
           __  __                                    
          |. ||. |    .|                             
          || ||| |    | |                            
          |: ||: |    |'|                   ._____   
          |  ||  |   |  |     .--'|  .---"| |.   |'  
      _   |  ||  |-. |  | __  |.  | _|__  | ||   |__ 
   .-'|  _|  ||  | ||   '-  | ||   |   |' | |    | |'
   |' | |.|  ||  | ||       '-'    |   |  | |    | | 
___|  '-' '  ''  ' ""       '      `   -  |_'    ' |____
 
________________________________________________________
 
					Joachim J Prinsloo
________________________________________________________
 
*/