在 SQL Server 中的存储过程中搜索文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14704105/
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
Search text in stored procedure in SQL Server
提问by DharaPPatel
I want to search a text from all my database stored procedures. I use the below SQL:
我想从我所有的数据库存储过程中搜索文本。我使用以下 SQL：
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 '%[ABD]%';
I want to search for [ABD]in all stored procedures including square brackets, but it's not giving the proper result. How can I change my query to achieve this?
我想[ABD]在包括方括号在内的所有存储过程中进行搜索，但它没有给出正确的结果。如何更改我的查询以实现此目的？
采纳答案by Mahmoud Gamal
Escape the square brackets:
转义方括号：
...
WHERE m.definition Like '%\[ABD\]%' ESCAPE '\'
Then the square brackets will be treated as a string literals not as wild cards.
然后方括号将被视为字符串文字而不是通配符。
回答by user27332
Try this request:
试试这个请求：
Query
询问
SELECT name
FROM sys.procedures
WHERE Object_definition(object_id) LIKE '%strHell%'
回答by David Smithers
Have you tried using some of the third party tools to do the search? There are several available out there that are free and that saved me a ton of time in the past.
您是否尝试过使用一些第三方工具进行搜索？有几个免费的可用，过去为我节省了大量时间。
Below are two SSMS Addins I used with good success.
以下是我成功使用的两个 SSMS 插件。
ApexSQL Search– Searches both schema and data in databases and has additional features such as dependency tracking and more…
ApexSQL 搜索– 搜索数据库中的架构和数据，并具有附加功能，例如依赖项跟踪等……
SSMS Tools pack– Has same search functionality as previous one and several other cool features. Not free for SQL Server 2012 but still very affordable.
SSMS 工具包– 具有与前一个相同的搜索功能和其他几个很酷的功能。SQL Server 2012 不是免费的，但仍然非常实惠。
I know this answer is not 100% related to the questions (which was more specific) but hopefully others will find this useful.
我知道这个答案不是 100% 与问题相关（更具体），但希望其他人会发现这很有用。
回答by Daniel Kelley
I usually run the following to achieve that:
我通常运行以下命令来实现：
select distinct object_name(id)
from syscomments
where text like '%[ABD]%'
order by object_name(id)
回答by pedram
Good practice to work with SQL Server.
使用 SQL Server 的良好做法。
Create below stored procedure and set short key,
创建以下存储过程并设置快捷键，
CREATE PROCEDURE [dbo].[Searchinall]
(@strFind AS VARCHAR(MAX))
AS
BEGIN
SET NOCOUNT ON;
--TO FIND STRING IN ALL PROCEDURES
BEGIN
SELECT OBJECT_NAME(OBJECT_ID) SP_Name
,OBJECT_DEFINITION(OBJECT_ID) SP_Definition
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'
END
--TO FIND STRING IN ALL VIEWS
BEGIN
SELECT OBJECT_NAME(OBJECT_ID) View_Name
,OBJECT_DEFINITION(OBJECT_ID) View_Definition
FROM sys.views
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'
END
--TO FIND STRING IN ALL FUNCTION
BEGIN
SELECT ROUTINE_NAME Function_Name
,ROUTINE_DEFINITION Function_definition
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%'+@strFind+'%'
AND ROUTINE_TYPE = 'FUNCTION'
ORDER BY
ROUTINE_NAME
END
--TO FIND STRING IN ALL TABLES OF DATABASE.
BEGIN
SELECT t.name AS Table_Name
,c.name AS COLUMN_NAME
FROM sys.tables AS t
INNER JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%'+@strFind+'%'
ORDER BY
Table_Name
END
END
Now - Set short key as below,
现在 - 设置快捷键如下，
So next time whenever you want to find a particular text in any of the four objects like Store procedure, Views, Functionsand Tables. You just need to write that keyword and press shortcut key.
因此，下一次，只要你想找到任何类似的四个对象的特定文本Store procedure，Views，Functions和Tables。您只需要编写该关键字并按快捷键即可。
For example:I want to search 'PaymentTable' then write 'PaymentTable' and make sure you select or highlight the written keyword in query editor and press shortcut key ctrl+4- it will provide you full result.
例如：我想搜索“PaymentTable”然后写“PaymentTable”并确保您在查询编辑器中选择或突出显示所写的关键字，然后按快捷键ctrl+4- 它会为您提供完整的结果。
回答by steoleary
Redgate's SQL Searchis a great tool for doing this, it's a free plugin for SSMS.
Redgate 的 SQL Search是一个很好的工具，它是 SSMS 的免费插件。
回答by Milan
Please take this as a "dirty" alternativebut this saved my behind many times especially when I was not familiar with the DB project. Sometimes you are trying to search for a string within all SPs and forget that some of the related logic may have been hiding between Functions and Triggersor it can be simply worded differently than you thought.
请将此视为“肮脏”的替代方案，但这使我多次落后，尤其是当我不熟悉 DB 项目时。有时您试图在所有 SP 中搜索一个字符串，却忘记了一些相关逻辑可能隐藏在函数和触发器之间，或者它的措辞可能与您想象的不同。
From your MSSMS you may right click your DB and select
Tasks -> Generate Scriptswizard to output all the SPs, Fns and Triggers into a single .sql file.
在您的 MSSMS 中，您可以右键单击您的数据库并选择
Tasks -> Generate Scripts向导将所有 SP、Fns 和触发器输出到单个 .sql 文件中。
Make sure to select Triggers too!
确保也选择触发器！
Then just use Sublime or Notepadto search for the string you need to find. I know this may be quite inefficient and paranoid approach but it works :)
然后只需使用 Sublime 或记事本搜索您需要查找的字符串。我知道这可能是非常低效和偏执的方法，但它有效:)
回答by user2846553
You can also use this one:
你也可以使用这个：
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION like '%Search_String%'
回答by Anil Singh
It might help you!
或许能帮到你！
SELECT DISTINCT
A.NAME AS OBJECT_NAME,
A.TYPE_DESC
FROM SYS.SQL_MODULES M
INNER JOIN SYS.OBJECTS A ON M.OBJECT_ID = A.OBJECT_ID
WHERE M.DEFINITION LIKE '%['+@SEARCH_TEXT+']%'
ORDER BY TYPE_DESC
回答by user1001101
select top 10 * from
sys.procedures
where object_definition(object_id) like '%\[ABD\]%'
