Wednesday, March 26, 2014

Script to view Dependencies for Database in SQL Server

SQL Server is providing feature to view Dependencies in SQL Server. To view object dependencies, we are following below steps in SQL Server Management Studio.
1.       Right click the object and click on View Dependencies.















2.  In that, we have two options
a.  Objects that depend on our object.

b.      Objects on which our object depends


   






Now, we will try to understand, How SQL Server is fetching this details. To achieve that, I ran SQL Server Profiler and found scripts. I used these scripts and create procedure sc.cp_find_referencing_objects with below parameters.
1.       @p_find_referencing_objects:
a.       1 = to fetch objects that depend on our object.
b.      0 = to fetch Objects on which our object depends
2.       @p_object_id
a.       Object_id from sys.objects table
Now, we will try to use this procedure to view object dependencies.
a.       Objects that depend on dbo.cp_insert_error_log.
Query:
select object_id from sys.objects where name = 'cp_insert_error_log'
Output:


EXEC sc.cp_find_referencing_objects
       @p_find_referencing_objects = 1,
       @p_object_id = 293576084

Below are details of each variable.
Output:

b.      Objects on which dbo.cp_insert_error_log depends.
Query:
EXEC sc.cp_find_referencing_objects
       @p_find_referencing_objects = 0,
       @p_object_id = 293576084
Output:
Now, we will use above procedure and try to find dependencies for well knows objects like Table, Function, View and Procedure.
Query:
create table #tmpObject
(
       object_id int,
       object_name sysname collate database_default,
       object_schema sysname collate database_default,
       object_db sysname,
       object_svr sysname,
       object_type smallint,
       relative_id int,
       relative_name sysname collate database_default,
       relative_schema sysname collate database_default,
       relative_db sysname,
       relative_svr sysname,
       relative_type smallint,
       schema_bound bit,
       ptype int,
       pname sysname,
       pschema sysname
)
Declare object_cursor Cursor DYNAMIC
For
Select
       object_id
From
       sys.objects
Where
       type in (
              -- Table
              'U',
              -- Function
              'TF','FN','IF','FS','FT',
              -- View
              'V',
              -- Procedure
              'P','RF','PC'
       )
       and object_id not in(select relative_id from #tmpObject)
Order by
       type,
       name
Open object_cursor
Declare
       @v_object_id int
      
Fetch Next From object_cursor INTO @v_object_id
While (@@FETCH_STATUS <> -1)
Begin
       If not exists(select 1 from  #tmpObject where relative_id = @v_object_id)
       Begin
              -- Insert into temporary object
              Insert into #tmpObject(
                     object_id,
                     object_name,
                     object_schema,
                     object_db,
                     object_svr,
                     object_type,
                     relative_id,
                     relative_name,
                     relative_schema,
                     relative_db,
                     relative_svr,
                     relative_type,
                     schema_bound,
                     ptype,
                     pname,
                     pschema
              )
              -- Objects which are depeneded on it.
              EXEC sc.cp_find_referencing_objects
                     @p_find_referencing_objects = 0,
                     @p_object_id = @v_object_id
       End
       Fetch Next From object_cursor INTO @v_object_id
End
-- Close Cursor
Close object_cursor
-- Deallocate Cursor
Deallocate object_cursor
-- Delete for same object reference
Delete from #tmpObject where object_id = relative_id
-- Delete duplicate entries
Delete from T
from
(
       Select
              object_id,
              relative_id,
              row_number() over(partition by object_id, relative_id order by object_id, relative_id) rownumber
       from
              #tmpObject
)T
Where
       rownumber > 1
-- Select
Select
       relative_db,
       case relative_type
              when 3 then 'Table'
              when 0 then 'Function'
              when 2 then 'View'
              when 4 then 'Procedure'
              when 6 then 'Default object'
              when 7 then 'Rule'
              when 8 then 'Trigger'
              when 11 then 'Aggregate function (CLR)'
              when 12 then 'Synonym'
              when 13 then 'Sequence'
              when 101 then 'Type'
              when 102 then 'XML_SCHEMA_COLLECTION'
              when 103 then 'Type'
              when 1000 then 'Assembly'
              when 201 then 'Partition Scheme'
              when 202 then 'Partition Function'
              when 104 then 'Type'
              when 203 then '203'
              when 1001 then 'Unknown'
              when 204 then 'Plan Guide'
              else cast(relative_type as varchar)
       end relative_type,
       relative_schema,
       relative_name,
       object_db,
       case object_type
              when 3 then 'Table'
              when 0 then 'Function'
              when 2 then 'View'
              when 4 then 'Procedure'
              when 6 then 'Default object'
              when 7 then 'Rule'
              when 8 then 'Trigger'
              when 11 then 'Assembly'
              when 12 then 'Synonym'
              when 13 then 'Sequence'
              when 201 then 'Partition Scheme'
              else cast(object_type as varchar)
       end object_type,
       object_schema,
       object_name
From
       #tmpObject
Order by
       relative_db,
       relative_type,
       relative_schema,
       relative_name,
       object_type,
       object_schema,
       object_name,
-- Drop temporary objects
Drop table #tmpObject

Output:









Below are scripts to download:
1. Find referencing objects.sql

No comments:

Post a Comment