按名称查找存储过程

Question

在SQL Server Management Studio中，有没有一种方法可以按名称或部分名称查找存储过程？（在活动数据库上下文中）

谢谢帮助

- gruber

Answer 1

你可以使用以下代码：

select * 
from 
   sys.procedures 
where 
   name like '%name_of_proc%'

如果你需要代码，你可以查看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%'

编辑更新：

你也可以使用 ANSI 标准版本。

SELECT * 
FROM 
    INFORMATION_SCHEMA.ROUTINES 
WHERE 
    ROUTINE_NAME LIKE '%name_of_proc%'

- Preet Sangha

---

如果您想获取名称为SPNAME的存储过程文本，使用“sp_helptext SPNAME”会更容易。 - Harry Sarshogh

在SQL 2012中，需要执行inner join sys.procedures p on p.object_id = c.id来进行内连接操作。 - user5226582

对于 SQL Server，ANSI 标准版本会在 8k 处截断 ROUTINE_DEFINITION。如果这是一个问题，您可以使用 object_definition(object_id(r.ROUTINE_NAME)) 作为替代方案。 - Mark Schultheiss

如果您不确定存储过程所在的目录（数据库）和模式，可以使用符合ANSI标准的版本并返回它们。例如，我发现一个代理作业引用了一个我无法轻易找到的存储过程；而符合ANSI标准的查询则为我解决了这个问题。 - youcantryreachingme

Answer 2

假设您正在对象资源管理器详细信息(F7)中查看存储过程列表，单击筛选器按钮并输入名称(或部分名称)。

- Codesleuth

---

非常感谢，我之前不知道过滤器的事情，不知怎么就从来没看到那个按钮。 - Chad Portman

Answer 3

这将对表格、视图（以及其他内容）起作用，而不仅仅是存储过程：

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

此外，它还提供了模式名称，在任何非平凡的数据库中都非常有用（例如需要通过名称查询存储过程的数据库）。

- nathanchere

---

将一个遗留应用程序从SQL 2005迁移到2016时，我的脚本出现了“在'@errorMessage'附近的语法不正确”，这并没有什么帮助。当我在SSSM中运行相同的SQL时，它添加了“在过程'some_name'中”，但是根本没有任何存储过程！结果证明它是一个触发器，而您的查询找到了它。谢谢！ - MickeyfAgain_BeforeExitOfSO

Answer 4

当我有一个存储过程名称，但不知道它属于哪个数据库时，我使用以下方法 -

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

- Radagast_Brown

---

非常感谢。这是唯一的有效解决方案，当你不知道数据库名称时也可以使用！有没有办法将存储过程名称声明为变量？我无法使其工作，猜想你也尝试过。 - Marjan Radfar

Answer 5

选项 1: 在 SSMS 中进入 查看 > 对象资源管理器详细信息（或按下F7）。在搜索框中输入并按下Enter。最后，在显示的列表中，右键单击并选择同步以在对象资源管理器树中找到该对象。

选项2：安装像dbForge Search这样的插件。然后右键单击显示的列表，选择在对象资源管理器中查找。

- Luis Hernandez

---

谢谢Luis Hernandez - Koba

Answer 6

您可以使用以下查询：

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')

如您所见，您也可以在存储过程的主体中进行搜索。

- Ardalan Shahgholi

Answer 7

对于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%';

- Sergey Nasonov

Answer 8

我在尝试 SQL 注入时发现了一个非常巧妙的诀窍，在对象浏览器中，只需使用百分比字符即可在搜索框中搜索存储过程、函数、视图、表、架构、索引等所有内容... 我累了，再想更多 :)

搜索模式

- Ernest Gunning