Posted By

joachimpr on 02/16/17

Tagged

object

Versions (?)

Last Edited at 02/16/17 05:35pm

Statistics

Viewed 600 times

Favorited by 0 user(s)

Related snippets

Object Dependencies TSQL

/ Published in: SQL

Get all SQL Server object dependencies

Highlighted TextExpand

--exec sp_GetObjectDependencies @IncludeConstraints = 0

Create procedure sp_GetObjectDependencies
(
	@ObjectToCheck		varchar(100) = '',
	@IncludeConstraints	bit = 0,
	@Help				bit = 0
)

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
________________________________________________________

URL: http://jjprinsloo.co.za

Report this snippet Tweet