（4.27）查看存储过程中的依赖关系

关键词：查看存储过程中引用的表，查看表在哪些存储过程中出现过

很多时候需要找到存储过程所依赖的对象。博客描述了一种在SQL服务器中查找存储过程依赖关系的方法。

以下查询创建存储过程。

 1.创建存储过程查询依赖

IF object_id('usp_getpersonaddress') is not null
drop proc usp_getpersonaddress
GO
Create procedure usp_getpersonaddress
AS
SELECT pp.BusinessEntityID,pp.FirstName + ' ' + pp.LastName,
pa.AddressLine1,pa.City
FROM
Person.Person pp join Person.Address pa
ON pp.BusinessEntityID = pa.AddressID
GO

以下查询列出了usp_getpersonaddress过程所依赖的所有对象。

-- find dependency
SELECT referencing_id,OBJECT_SCHEMA_NAME ( referencing_id )
+ '.' +
    OBJECT_NAME(referencing_id) AS referencing_object_name,
    obj.type_desc AS referencing_object_type,
    referenced_schema_name + '.' +
    referenced_entity_name As referenced_object_name
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS obj ON sed.referencing_id = obj.object_id
WHERE referencing_id =OBJECT_ID('usp_getpersonaddress')
GO

 

上述查询的输出如下所示。

　　　　

 

2、查看与表相关的存储过程

　　

SELECT DISTINCT objects.name, objects.type,
comments.text proc_defintion
FROM syscomments comments
INNER JOIN sys.objects objects 
ON comments.id=objects.object_id
WHERE comments.text LIKE '%Person.Address%'
AND objects.type='P'

3、查看与列相关的存储过程

-- find stored procedure related to a column in database
SELECT DISTINCT objects.name, objects.type,
comments.text proc_defintion
FROM syscomments comments
INNER JOIN sys.objects objects 
ON comments.id=objects.object_id
WHERE comments.text LIKE '%AddressID%'
AND objects.type='P'