Write a query that returns object dependencies

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 select "View Dependencies"
• Now you can navigate the dependencies back and forth.

How to get the same information programmatically? (SQL query?)

Answer 1

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

Answer 2

Here is another easy 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

Answer 3

sys INFORMATION_SCHEMA.

, , .

Answer 4

, , , , .

sys.sql_dependencies sys.dm_sql_referencing_entities.

, :

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

Answer 5

.

 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