如何使用 SQL Server 2008 基于表名搜索查找存储过程的名称？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6924257/
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 find the name of stored procedure, based on table name search, using SQL Server 2008?
提问by Microsoft Developer
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?
有什么方法可以使用搜索查询来找到存储过程吗？
I have tried this code:
我试过这个代码：
SELECT distinct so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '% RejectionReason %'
Where RejectionReasonis my table name, but it shows all procedures where RejectionReasonis used as column name, so that doesn't work.
RejectionReason我的表名在哪里，但它显示了RejectionReason用作列名的所有过程，因此不起作用。
回答by Carlos Quintanilla
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'
or
或者
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%'
or
或者
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/
如果这些都没有帮助您查看此博客：http: //blog.sqlauthority.com/2010/02/04/sql-server-get-the-list-of-object-dependencies-sp_depends-and-information_schema-routines-和-sys-dm_sql_reference_entities/
回答by Alex_L
Try to use RedGate's free tool SQL Search.
尝试使用 RedGate 的免费工具SQL Search。
回答by Praveen04
Here is a piece of code hope it will work. Just changes the table name it depends upon your code
这是一段代码，希望它能工作。只需更改表名，这取决于您的代码
SELECT DISTINCT so.name
FROM syscomments sc INNER JOIN sysobjects so on sc.id=so.id
WHERE sc.text LIKE '%tablename%'
e.g.:
例如：
SELECT DISTINCT so.name
FROM syscomments sc INNER JOIN sysobjects so on sc.id=so.id
WHERE sc.text LIKE '%users%'
You will get the list of store procedures and the table relations.
您将获得存储过程列表和表关系。
回答by VS1
As per MSDNsp_dependswill be removed in future releases in case you are using that, you can use the following queryinstead:
根据MSDNsp_depends将在未来版本中删除，以防您使用它，您可以使用以下查询：
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.TableName', 'OBJECT');
回答by Matt
I am using the following SQL script to search for column namesand text inside all stored procedures of your database. You can use it as well to find tables in stored procedures.
我正在使用以下 SQL 脚本在您的数据库的所有存储过程中搜索列名和文本。您也可以使用它在存储过程中查找表。
Specify the search term in variable @SearchFor(as you would use it in a LIKEexpression, e.g. '%LastName%'to find columns and stored procedures containing LastName).
在变量中指定搜索词@SearchFor（就像在LIKE表达式中使用它一样，例如'%LastName%'查找包含 的列和存储过程LastName）。
It will find column names in tables as well as text inside stored procedures. The script can even display the SP source code, if you set @SPNameOnlyto 0.
它将查找表中的列名以及存储过程中的文本。如果设置@SPNameOnly为 0 ，脚本甚至可以显示 SP 源代码。
--
-- Purpose: Search field names in all tables, views stored procedures
--
DECLARE @SearchFor nvarchar(max)='%Search_SP_Or_Table_Or_View%' -- search for this string
DECLARE @SearchSP bit = 1 -- 1=search in SPs as well
DECLARE @DisplaySPSource bit = 1 -- 1=display SP source code
-- tables
if (@SearchSP=1) begin
(
select '['+c.table_Schema+'].['+c.table_Name+'].['+c.column_name+']' [schema_object],
t.table_type
from information_schema.columns c
left join information_schema.Tables t on c.table_name=t.table_name
where column_name like @SearchFor or t.table_name like @SearchFor
UNION
select '['+routine_Schema+'].['+routine_Name+']' [schema_object],
'PROCEDURE' as table_type from information_schema.routines
where routine_definition like @SearchFor or routine_name like @SearchFor
and routine_type='procedure'
)
order by table_type, schema_object
end else begin
select '['+c.table_Schema+'].['+c.table_Name+'].['+c.column_name+']' [schema_object],
t.table_type
from information_schema.columns c
left join information_schema.Tables t on c.table_name=t.table_name
where column_name like @SearchFor or t.table_name like @SearchFor
order by c.table_Name, c.column_name
end
-- stored procedure (source listing)
if (@SearchSP=1) begin
if (@DisplaySPSource=1) begin
select '['+routine_Schema+'].['+routine_Name+']' [schema.sp], routine_definition
from information_schema.routines
where routine_definition like @SearchFor or routine_name like @SearchFor
and routine_type='procedure'
order by routine_name
end
end
回答by Nalan Madheswaran
This will return SP's and Views.
这将返回 SP 和视图。
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 '%TableName%'
回答by Karthi Rathinavelu
SysObjects stores basic information about all objects inside your database. It's useful for you to know because it tells us the name of each object and the type of the object.
SysObjects 存储有关数据库中所有对象的基本信息。了解它对您很有用，因为它告诉我们每个对象的名称和对象的类型。
SysComments stores the actual text (code) for your stored procedures and functions. It contains an ID field that maps back to the id field in SysObjects.
SysComments 存储存储过程和函数的实际文本（代码）。它包含一个映射回 SysObjects 中的 id 字段的 ID 字段。
select so.name, text
from sysobjects so, syscomments sc
where so.id = sc.id
and text like '%RejectionReason%'
回答by BonyT
There are two possibilities I am aware of.
我知道有两种可能性。
Firstly SQL Management Studio has an option to show Dependencies. Right-click on the Table and select View DependenciesHowever, this will not highlight usps where the tablename is embedded in dynamic SQL.
首先，SQL Management Studio 有一个显示依赖项的选项。右键单击表并选择View Dependencies但是，这不会突出显示表名嵌入在动态 SQL 中的 usps。
The second option is to right click on the database and select Generate Scripts. Follow the wizard and script all the usps to a new query window, then search that for the name of your table. This is more laborious, but will find all uses.
第二个选项是右键单击数据库并选择Generate Scripts. 按照向导将所有 usps 脚本化到一个新的查询窗口，然后在该窗口中搜索您的表的名称。这更费力，但会找到所有用途。
回答by Sumit
I guess this script shows all the dependent object of the table, including SPs.
我猜这个脚本显示了表的所有依赖对象，包括 SP。
USE MYDatabase
GO
DECLARE @TableName varchar(100)
SET @TableName = 'mytable'
SELECT
SourceSchema = OBJECT_SCHEMA_NAME(sed.referencing_id)
,SourceObject = OBJECT_NAME(sed.referencing_id)
,ReferencedDB = ISNULL(sre.referenced_database_name, DB_NAME())
,ReferencedSchema = ISNULL(sre.referenced_schema_name,
OBJECT_SCHEMA_NAME(sed.referencing_id))
,ReferencedObject = sre.referenced_entity_name
,ReferencedColumnID = sre.referenced_minor_id
,ReferencedColumn = sre.referenced_minor_name
FROM sys.sql_expression_dependencies sed
CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id)
+ '.' + OBJECT_NAME(sed.referencing_id), 'OBJECT') sre
WHERE sed.referenced_entity_name = @TableName
AND sre.referenced_entity_name = @TableName
for more details you can check out. http://sqlserverplanet.com/sql-server-2008/find-dependent-objects/
有关更多详细信息，您可以查看。 http://sqlserverplanet.com/sql-server-2008/find-dependent-objects/
