Write a query that returns the dependencies of an object

Question

I'm looking for exactly what Management Studio shows with the "View Dependencies" menu.

Management Studio connected to SQL Server 2008
Right click on an object and choose "View Dependencies"
Now you can navigate through the dependencies back and forth.

How do I get the same information programmatically? (an SQL query?)

score 4 · Accepted Answer · answered Dec 16 '09 at 18:07

Before you run the following query, replace <database_name> and <schema_name.function_name> with valid names

USE <database_name>;
GO
SELECT OBJECT_NAME(object_id) AS referencing_object_name
    ,COALESCE(COL_NAME(object_id, column_id), '(n/a)') AS referencing_column_name
    ,*
FROM sys.sql_dependencies
WHERE referenced_major_id = OBJECT_ID('<schema_name.function_name>')
ORDER BY OBJECT_NAME(object_id), COL_NAME(object_id, column_id);
GO

score 3 · Answer 2 · answered Apr 18 '13 at 17:44

I know this is an older question, but I also know I looked at it myself while coming up with my own solution.

Rather than using the deprecated sys.sql_dependencies, you can use the sys.dm_sql_referencing_entities table valued function.

The following query calls it recursively to trace down dependencies, showing each step in the dependency chain:

DECLARE @table varchar(max);
SET @table = 'schema.objectname';
;with 
DepsOn As (
    SELECT CAST(@table As varchar(max)) As parent
         , CAST(l1.referencing_schema_name 
               + '.' 
               + l1.referencing_entity_name As varchar(max)) As child
         , l1.referencing_class_desc As [description]
         , 0 As Depth
    FROM sys.dm_sql_referencing_entities(@table,'OBJECT') l1
    UNION ALL
    SELECT l2.child As parent
         , cast(l2ca.referencing_schema_name 
               + '.' 
               + l2ca.referencing_entity_name As varchar(max)) As child
         , l2ca.referencing_class_desc As [description]
         , l2.Depth + 1 As Depth
    FROM DepsOn l2
    CROSS APPLY sys.dm_sql_referencing_entities(l2.child,'OBJECT') l2ca
    )
SELECT *
FROM DepsOn

score 3 · Answer 3 · answered Dec 16 '09 at 18:16

Here is another simpler way:

  SELECT DISTINCT
    O.ID ,
    O.Name AS TableName ,
    O.xtype
  FROM
    sysObjects O ( NOLOCK )
  INNER JOIN sysComments C ( NOLOCK ) ON O.ID = C.ID
  WHERE
    C.text LIKE '%<schema_name.function_name>%'
  ORDER BY
    XType ,
    TableName
Before you run the following query, replace <schema_name.function_name> with a valid name

score 1 · Answer 4 · edited May 23 '17 at 12:23

1

Have a look at the metadata in the sys and INFORMATION_SCHEMA tables.

There is this answer, this answer, and this answer, which could all be useful.

edited May 23 '17 at 12:23

Community

1
1

answered Dec 16 '09 at 18:04

Cade Roux

88,164
40
182
265

Yes... I've seen several solutions... but I'm being lazy and want the bullet proof solution without mine doing the trial and error :-) – Nestor Dec 16 '09 at 18:07
No problem - you asked how to qrite a query - not give me one! ;-) – Cade Roux Dec 16 '09 at 18:13

score 0 · Answer 5 · answered Jul 17 '15 at 13:02

Tested Code . I run it and go the my required output

 SELECT referencing_schema_name, referencing_entity_name,
     referencing_id, referencing_class_desc, is_caller_dependent
     FROM sys.dm_sql_referencing_entities ('dbo.yourobject', 'OBJECT');
     GO

VishalDream

Write a query that returns the dependencies of an object

5 Answers5

Linked