Write a query that returns the dependencies of an object
I'm looking for exactly what Management Studio shows wit开发者_如何学编程h 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?)
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
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
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
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.
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
精彩评论