Posted By

joachimpr on 02/16/17


Tagged

object dependencies


Versions (?)

Object Dependencies TSQL


 / Published in: SQL
 

URL: http://jjprinsloo.co.za

Get all SQL Server object dependencies

  1. --exec sp_GetObjectDependencies @IncludeConstraints = 0
  2.  
  3. CREATE procedure sp_GetObjectDependencies
  4. (
  5. @ObjectToCheck varchar(100) = '',
  6. @IncludeConstraints bit = 0,
  7. @Help bit = 0
  8. )
  9.  
  10. AS
  11.  
  12. IF @Help = 1
  13. BEGIN
  14. SELECT N'@Help' AS [Parameter Name] ,
  15. N'BIT' AS [DATA Type] ,
  16. N'Displays this help message.' AS [Parameter Description]
  17.  
  18. UNION ALL
  19. SELECT N'@ObjectToCheck',
  20. N'VARCHAR(100)',
  21. N'Specify a specific object of which to check the dependancies of. Default is to check all objects.'
  22.  
  23. UNION ALL
  24. SELECT N'@IncludeConstraints',
  25. N'BIT',
  26. N'Specify whether you want to include contraints and keys for the objects being checked for it''s dependancies. Default is to exclude constraints'
  27. END
  28. ELSE
  29. BEGIN
  30. declare @Objects AS TABLE
  31. (
  32. ID int IDENTITY(1,1),
  33. SchemaName varchar(100),
  34. ObjectName varchar(100)
  35. )
  36.  
  37. declare @Dependancies AS TABLE
  38. (
  39. SourceObject varchar(200),
  40. SourceObjectType varchar(200),
  41. SourceObjectLastExecutionDate datetime,
  42. SourceObjectCreateDate datetime,
  43. SourceObjectModifiedDate datetime,
  44. SourceObjectIsMSShipped bit,
  45. ReferencingObject varchar(200),
  46. ReferencingObjectType varchar(200),
  47. ReferencingObjectLastExecutionDate datetime,
  48. ReferencingObjectCreateDate datetime,
  49. ReferencingObjectModifiedDate datetime,
  50. ReferencingObjectIsMSShipped bit,
  51. IsReferencingObjectDependant bit
  52. )
  53.  
  54. declare @Constraints AS TABLE
  55. (
  56. SourceObject varchar(200),
  57. SourceObjectType varchar(200),
  58. SourceObjectLastExecutionDate datetime,
  59. SourceObjectCreateDate datetime,
  60. SourceObjectModifiedDate datetime,
  61. SourceObjectIsMSShipped bit,
  62. ReferencingObject varchar(200),
  63. ReferencingObjectType varchar(200),
  64. ReferencingObjectLastExecutionDate datetime,
  65. ReferencingObjectCreateDate datetime,
  66. ReferencingObjectModifiedDate datetime,
  67. ReferencingObjectIsMSShipped bit,
  68. IsReferencingObjectDependant bit
  69. )
  70.  
  71. INSERT INTO @Objects(SchemaName, ObjectName)
  72. SELECT
  73. s.name AS SchemaName,
  74. o.name AS ObjectName
  75. FROM
  76. sys.objects o
  77. INNER JOIN
  78. sys.schemas s
  79. ON
  80. s.schema_id = o.schema_id
  81. WHERE
  82. o.name = (case when @ObjectToCheck = '' then o.name else @ObjectToCheck end)
  83.  
  84. declare @CurrentRow int
  85. declare @MaxRow int
  86.  
  87. SET @CurrentRow = (SELECT MIN(ID) FROM @Objects)
  88. SET @MaxRow = (SELECT MAX(ID) FROM @Objects)
  89.  
  90. while @CurrentRow <= @MaxRow
  91. begin
  92. declare @CurrentObjectName varchar(100)
  93. declare @CurrentObjectSchema varchar(100)
  94. declare @CurrentObject varchar(200)
  95. declare @CurrentObjectID bigint
  96.  
  97. SET @CurrentObjectName = (SELECT ObjectName FROM @Objects WHERE ID = @CurrentRow)
  98. SET @CurrentObjectSchema = (SELECT SchemaName FROM @Objects WHERE ID = @CurrentRow)
  99. SET @CurrentObject = (SELECT SchemaName + '.' + ObjectName FROM @Objects WHERE ID = @CurrentRow)
  100. SET @CurrentObjectID = (SELECT object_id FROM sys.objects o
  101. INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
  102. WHERE o.name = @CurrentObjectName AND s.name = @CurrentObjectSchema)
  103.  
  104. INSERT INTO @Dependancies
  105. SELECT
  106. @CurrentObject AS SourceObject,
  107. so.type_desc AS SourceObjectType,
  108. ss.last_execution_time AS SourceObjectLastExecutionDate,
  109. so.create_date AS SourceObjectCreateDate,
  110. so.modify_date AS SourceObjectModifiedDate,
  111. so.is_ms_shipped AS SourceObjectIsMSShipped,
  112. re.referencing_schema_name + '.' + re.referencing_entity_name AS ReferencingObject,
  113. ro.type_desc AS ReferencingObjectType,
  114. rs.last_execution_time AS ReferencingObjectLastExecutionDate,
  115. ro.create_date AS ReferencingObjectCreateDate,
  116. ro.modify_date AS ReferencingObjectModifiedDate,
  117. ro.is_ms_shipped AS ReferencingObjectIsMSShipped,
  118. re.is_caller_dependent AS IsReferencingObjectDependant
  119.  
  120. FROM
  121. sys.dm_sql_referencing_entities (@CurrentObject, 'OBJECT') re
  122. LEFT JOIN
  123. sys.objects so
  124. ON
  125. so.object_id = @CurrentObjectID
  126. LEFT JOIN
  127. sys.objects ro
  128. ON
  129. ro.object_id = re.referencing_id
  130. LEFT JOIN
  131. sys.dm_exec_procedure_stats ss
  132. ON
  133. ss.object_id = so.object_id
  134. LEFT JOIN
  135. sys.dm_exec_procedure_stats rs
  136. ON
  137. rs.object_id = ro.object_id
  138.  
  139. INSERT INTO @Constraints
  140. SELECT DISTINCT
  141. @CurrentObject AS SourceObject,
  142. so.type_desc AS SourceObjectType,
  143. NULL AS SourceObjectLastExecutionDate,
  144. so.create_date AS SourceObjectCreateDate,
  145. so.modify_date AS SourceObjectModifiedDate,
  146. so.is_ms_shipped AS SourceObjectIsMSShipped,
  147. rs.name + '.' + ro.name AS ReferencingObject,
  148. ro.type_desc AS ReferencingObjectType,
  149. NULL AS ReferencingObjectLastExecutionDate,
  150. ro.create_date AS ReferencingObjectCreateDate,
  151. ro.modify_date AS ReferencingObjectModifiedDate,
  152. ro.is_ms_shipped AS ReferencingObjectIsMSShipped,
  153. 1 AS IsReferencingObjectDependant
  154. FROM
  155. sys.objects so
  156. LEFT JOIN
  157. sys.objects ro
  158. ON
  159. ro.parent_object_id = so.object_id
  160. LEFT JOIN
  161. sys.schemas rs
  162. ON
  163. rs.schema_id = ro.schema_id
  164. WHERE
  165. so.object_id = @CurrentObjectID
  166. AND
  167. so.type_desc <> 'INTERNAL_TABLE'
  168. AND
  169. ro.type IN ('C','D','F','PK','UQ')
  170.  
  171. SET @CurrentRow = @CurrentRow + 1
  172. end
  173.  
  174. IF(@IncludeConstraints = 0)
  175. begin
  176. SELECT DISTINCT * FROM @Dependancies
  177. WHERE ReferencingObjectType NOT LIKE '%CONSTRAINT%'
  178. ORDER BY SourceObject, ReferencingObject
  179. end
  180. else
  181. begin
  182. SELECT DISTINCT * FROM @Dependancies
  183. UNION
  184. SELECT DISTINCT * FROM @Constraints
  185. ORDER BY SourceObject, ReferencingObject
  186. end
  187. END
  188.  
  189. /*
  190.   __ __
  191.   |. ||. | .|
  192.   || ||| | | |
  193.   |: ||: | |'| ._____
  194.   | || | | | .--'| .---"| |. |'
  195.   _ | || |-. | | __ |. | _|__ | || |__
  196.   .-'| _| || | || '- | || | |' | | | |'
  197.   |' | |.| || | || '-' | | | | | |
  198. ___| '-' ' '' ' "" ' ` - |_' ' |____
  199.  
  200. ________________________________________________________
  201.  
  202. Joachim J Prinsloo
  203. ________________________________________________________
  204.  
  205. */

Report this snippet  

You need to login to post a comment.