SQL 如何识别引用特定表的所有存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7272157/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to identify all stored procedures referring a particular table
提问by DJay
I created a table on development environment for testing purpose and there are few sp's which are refreing this table. Now I have have to drop this table as well as identify all sp's which are referring this table. I am facing difficulty to find list of all sp's. Please suggest some query by assuming that the table name is 'x' and database is sql server 2005.
我为测试目的创建了一个关于开发环境的表，很少有 sp 正在引用这个表。现在我必须删除这个表，并确定所有引用这个表的 sp。我很难找到所有 sp 的列表。请假设表名是“x”，数据库是 sql server 2005，提出一些查询建议。
回答by Chains
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TableNameOrWhatever%'
BTW -- here is a handy resource for this type of question: Querying the SQL Server System Catalog FAQ
顺便说一句——这里是这类问题的一个方便的资源：查询 SQL Server 系统目录常见问题解答
回答by Guy Hollington
The following works on SQL2008 and above. Provides a list of both stored procedures and functions.
以下适用于 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
回答by Pramod Pawar
sometimes above queries will not give correct result, there is built in stored procedure available to get the table dependencies as:
有时上述查询不会给出正确的结果，内置存储过程可用于获取表依赖项：
EXEC sp_depends @objname = N'TableName';
回答by Hans Ke?ing
A non-query way would be to use the Sql Server Management Studio.
一种非查询方式是使用 Sql Server Management Studio。
Locate the table, right click and choose "View dependencies".
找到该表，右键单击并选择“查看依赖项”。
EDIT
编辑
But, as the commenters said, it is not very reliable.
但是，正如评论者所说，它不是很可靠。
回答by Deepak Kothari
The following query will fetch all Stored Procedure names and the corresponding definition of those SP's
以下查询将获取所有存储过程名称以及这些 SP 的相应定义
select
so.name,
text
from
sysobjects so,
syscomments sc
where
so.id = sc.id
and UPPER(text) like '%<TABLE NAME>%'
回答by Tom H
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>%'
Just keep in mind that this will also turn up SPs where the table name is in the comments or where the table name is a substring of another table name that is being used. For example, if you have tables named "test" and "test_2" and you try to search for SPs with "test" then you'll get results for both.
请记住，这也会显示表名在注释中或表名是正在使用的另一个表名的子字符串的 SP。例如，如果您有名为“test”和“test_2”的表，并且您尝试搜索带有“test”的 SP，那么您将获得两者的结果。
回答by Siraj Ansari
The query below works only when searching for dependencies on a table and not those on a column:
下面的查询仅在搜索表上的依赖项而不是列上的依赖项时才有效：
EXEC sp_depends @objname = N'TableName';
However, the following query is the best option if you want to search for all sorts of dependencies, it does not miss any thing. It actually gives more information than required.
但是，如果您想搜索各种依赖项，以下查询是最佳选择，它不会遗漏任何东西。它实际上提供了比所需更多的信息。
select distinct
so.name
--, text
from
sysobjects so,
syscomments sc
where
so.id = sc.id
and lower(text) like '%organizationtypeid%'
order by so.name
回答by ric
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'table_name' + '%'
GO
This will work if you have to mention the table name.
如果您必须提及表名，这将起作用。
回答by nzrytmn
In management studio you can just right click to table and click to 'View Dependencies' 
在管理工作室中，您可以右键单击表格并单击“查看依赖项”
than you can see a list of Objects that have dependencies with your table :
您可以看到与您的表有依赖关系的对象列表：
回答by Alain Ghawi
You have basically 2 options:
您基本上有两个选择：
----Option 1
- - 选项1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
----选项2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'
These 2 queries will get you all the stored procedures that are referring the table you desire. This query relies on 2 sys tables which are sysobjects and syscomments. The sysobjects is where all of your DB object names are stored this includes the stored procedures.
这两个查询将为您提供引用所需表的所有存储过程。此查询依赖于 2 个 sys 表，它们是 sysobjects 和 syscomments。sysobjects 是存储所有数据库对象名称的地方，包括存储过程。
The syscomments contains the text for all of your procedures.
syscomments 包含所有过程的文本。
If you query: SELECT * FROM syscomments
如果查询： SELECT * FROM syscomments
You'll have a table containing the id which is the mapping to the sysobjects table with the text contained in the stored procedures as the last column.
您将有一个包含 id 的表，它是到 sysobjects 表的映射，存储过程中包含的文本作为最后一列。
