SQL 按名称查找存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3574244/
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
Find stored procedure by name
提问by gruber
Is there any way I can find in SQL Server Management Studio stored procedure by name or by part of the name? (on active database context)
有什么方法可以按名称或部分名称在 SQL Server Management Studio 存储过程中找到？（在活动数据库上下文中）
Thanks for help
感谢帮助
回答by Preet Sangha
You can use:
您可以使用：
select *
from
sys.procedures
where
name like '%name_of_proc%'
if you need the code you can look in the syscomments table
如果您需要代码，您可以查看 syscomments 表
select text
from
syscomments c
inner join sys.procedures p on p.object_id = c.object_id
where
p.name like '%name_of_proc%'
Edit Update:
编辑更新：
you can can also use the ansi standard version
你也可以使用ansi标准版
SELECT *
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_NAME LIKE '%name_of_proc%'
回答by Codesleuth
Assuming you're in the Object Explorer Details (F7) showing the list of Stored Procedures, click the Filters button and enter the name (or partial name).
假设您在F7显示存储过程列表的对象资源管理器详细信息 ( ) 中，单击过滤器按钮并输入名称（或部分名称）。
回答by nathanchere
This will work for tables and views (among other things) as well, not just sprocs:
这也适用于表和视图（除其他外），而不仅仅是 sproc：
SELECT
'[' + s.name + '].[' + o.Name + ']',
o.type_desc
FROM
sys.objects o
JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE
o.name = 'CreateAllTheThings' -- if you are certain of the exact name
OR o.name LIKE '%CreateAllThe%' -- if you are not so certain
It also gives you the schema name which will be useful in any non-trivial database (e.g. one where you need a query to find a stored procedure by name).
它还为您提供了模式名称，该名称在任何非平凡数据库中都很有用（例如，您需要查询以按名称查找存储过程的数据库）。
回答by Ardalan Shahgholi
You can use this query:
您可以使用此查询：
SELECT
ROUTINE_CATALOG AS DatabaseName ,
ROUTINE_SCHEMA AS SchemaName,
SPECIFIC_NAME AS SPName ,
ROUTINE_DEFINITION AS SPBody ,
CREATED AS CreatedDate,
LAST_ALTERED AS LastModificationDate
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
(ROUTINE_DEFINITION LIKE '%%')
AND
(ROUTINE_TYPE='PROCEDURE')
AND
(SPECIFIC_NAME LIKE '%AssessmentToolDegreeDel')
As you can see, you can do search inside the body of Stored Procedure also.
如您所见，您也可以在存储过程的主体内进行搜索。
回答by Radagast_Brown
When I have a Store Procedure name, and do not know which database it belongs to, I use the following -
当我有一个存储过程名称，并且不知道它属于哪个数据库时，我使用以下 -
Use [master]
GO
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
--Status 48 (mirrored db)
SELECT name FROM MASTER.dbo.sysdatabases WHERE STATUS NOT LIKE 48 AND name NOT IN ('master','model','msdb','tempdb','distribution')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @statement = 'SELECT * FROM ['+@dbname+'].INFORMATION_SCHEMA.ROUTINES WHERE [ROUTINE_NAME] LIKE ''%name_of_proc%'''+';'
print @statement
EXEC sp_executesql @statement
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
回答by Sergey Nasonov
For SQL Server version 9.0 (2005), you can use the code below:
对于 SQL Server 9.0 (2005) 版，您可以使用以下代码：
select *
from
syscomments c
inner join sys.procedures p on p.object_id = c.id
where
p.name like '%usp_ConnectionsCount%';
回答by Luis Hernandez
Option 1:In SSMS go to View > Object Explorer Detailsor press F7. Use the Searchbox. Finally in the displayed list right click and select Synchronizeto find the object in the Object Explorertree.
选项 1：在 SSMS 中转到View > Object Explorer Details或按 F7。使用Search盒子。最后在显示的列表中右键单击并选择Synchronize以在Object Explorer树中查找对象。
Option 2:Install an Add-On like dbForge Search. Right click on the displayed list and select Find in Object Explorer.
选项 2：安装类似dbForge Search. 右键单击显示的列表并选择Find in Object Explorer。
回答by Ernest Gunning
Very neat trick I stumble upon trying some SQL injection, in object explorer in the search box just use your percentage characters, and this will search EVERYTHING stored procedures, functions, views, tables, schema, indexes...I tired of thinking of more :)
非常巧妙的技巧我偶然尝试了一些 SQL 注入，在搜索框中的对象资源管理器中只需使用您的百分比字符，这将搜索所有存储过程、函数、视图、表、模式、索引......我厌倦了思考更多:)
