CODEWITHSUNDEEP
sql-serversql-server-2008dependencies
Nestor
Nestor

Reputation: 13990

Write a query that returns the dependencies of an object

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

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

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

Upvotes: 4

Views: 5112

Answers (5)

VishalDream
VishalDream

Reputation: 341

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

Upvotes: 0

Alex Mumme
Alex Mumme

Reputation: 85

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

Upvotes: 3

Scott and the Dev Team
Scott and the Dev Team

Reputation: 2431

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

Upvotes: 3

Scott and the Dev Team
Scott and the Dev Team

Reputation: 2431

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

Upvotes: 4

Cade Roux
Cade Roux

Reputation: 89671

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.

Upvotes: 1

Related Questions