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