如何识别引用特定表的所有存储过程

为了测试的目的，我创建了一个关于开发环境的表，并且很少有 sp 重新释放这个表。现在我必须删除这个表，并且识别所有引用这个表的 sp。我很难找到所有的名单。假设表名为‘ x’，数据库为 sqlserver2005，请建议使用一些查询。

597981 次浏览
小开 
SELECT
o.name
FROM
sys.sql_modules sm
INNER JOIN sys.objects o ON
o.object_id = sm.object_id
WHERE
sm.definition LIKE '%<table name>%'

请记住，如果表名出现在注释中，或者表名是正在使用的另一个表名的子字符串，那么这也会出现 SP。例如，如果有名为“ test”和“ test _ 2”的表，并尝试用“ test”搜索 SP，那么两个表都会得到结果。

小开
最佳答案 
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TableNameOrWhatever%'

顺便说一句——这里有一个方便的资源来解决这种类型的问题: 查询 SQL Server 系统目录常见问题

小开

非查询方式是使用 SqlServerManagementStudio。

找到表，右键单击并选择“查看依赖项”。

剪辑

但是，正如评论者所说，这并不十分可靠。

小开

以下内容适用于 SQL2008及以上版本。提供存储过程和函数的列表。

select distinct [Table Name] = o.Name, [Found In] = sp.Name, sp.type_desc
from sys.objects o inner join sys.sql_expression_dependencies  sd on o.object_id = sd.referenced_id
inner join sys.objects sp on sd.referencing_id = sp.object_id
and sp.type in ('P', 'FN')
where o.name = 'YourTableName'
order by sp.Name
小开

以下查询将获取所有存储过程名称以及这些 SP 的对应定义

select
so.name,
text
from
sysobjects so,
syscomments sc
where
so.id = sc.id
and UPPER(text) like '%<TABLE NAME>%'
小开

有时上述查询不会给出正确的结果，有内置的存储过程可用于获取表依赖项如下:

EXEC sp_depends @objname = N'TableName';
小开

下面的查询只在搜索表上的依赖项而不是列上的依赖项时有效:

EXEC sp_depends @objname = N'TableName';

但是，如果您想搜索所有类型的依赖项，以下查询是最佳选择，它不会遗漏任何东西。实际上它提供的信息比需要的多。

 select distinct
so.name
--, text
from
sysobjects so,
syscomments sc
where
so.id = sc.id
and lower(text) like '%organizationtypeid%'
order by so.name
小开 
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'table_name' + '%'


GO

如果您必须提到表名，那么这种方法就可以工作。

小开

在管理工作室，你可以右键点击表格，然后点击“查看依赖项”enter image description here

可以看到与表有依赖关系的对象列表:enter image description here

小开

试试这个

   SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%your table name%'
小开

你基本上有两个选择:

——选项1

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'

——选项2

SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

这两个查询将为您提供引用所需表的所有存储过程。这个查询依赖于两个 sys 表，它们是 sysoobject 和 syscomments。Sysoobject 是存储所有 DB 对象名称的地方，包括存储过程。

Syscomments 包含所有过程的文本。

如果你问: SELECT * FROM syscomments

您将拥有一个包含 id 的表，这是到 sysoObjects 表的映射，存储过程中包含的文本作为最后一列。

小开

如果您正在使用 Azure SQL/Synapse Anlaytics，这个有用的查询也可以工作

SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE m.definition Like '%Table_Name%'
小开 
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

提交答案