如何使用 SQL Server 2008 基于表名搜索查找存储过程的名称? [英] How to find the name of stored procedure, based on table name search, using SQL Server 2008?
问题描述
我想找到使用特定表的所有存储过程.数据库中有很多存储过程,因此无法检查每个过程.
I want to find all of the stored procedures where a particular table is being used. There are lots of stored procedures in the database, so it's not feasible to check each procedure.
有什么办法可以使用搜索查询来找到存储过程吗?
Is there any way to use a search query so that I can find the stored procedures?
我试过这个代码:
SELECT distinct so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '% RejectionReason %'
其中 RejectionReason
是我的表名,但它显示了将 RejectionReason
用作列名的所有过程,因此不起作用.
Where RejectionReason
is my table name, but it shows all procedures where RejectionReason
is used as column name, so that doesn't work.
推荐答案
SELECT o.name, o.type_desc, p.name, p.type_desc
FROM sys.sql_dependencies d
INNER JOIN sys.objects o
ON d.object_id = o.object_id
INNER JOIN sys.objects p
ON d.referenced_major_id = p.object_id
AND o.name = 'RejectionReason'
或
SELECT o.name, t.TABLE_NAME, c.text
FROM syscomments c
JOIN sysobjects o
ON c.id = o.id
JOIN INFORMATION_SCHEMA.Tables t
ON c.text LIKE '%RejectionReason%'
或
EXEC sp_depends @objname = N'RejectionReason';
if none of those help you check this blog: http://blog.sqlauthority.com/2010/02/04/sql-server-get-the-list-of-object-dependencies-sp_depends-and-information_schema-routines-and-sys-dm_sql_referencing_entities/
这篇关于如何使用 SQL Server 2008 基于表名搜索查找存储过程的名称?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!