在SQL服务器的存储过程中搜索文本

我通常使用以下方法来实现这一点：

select distinct object_name(id)from syscommentswhere text like '%[ABD]%'order by object_name(id)

去掉方括号：

...WHERE m.definition Like '%\[ABD\]%' ESCAPE '\'

然后方括号将被视为字符串文字而不是通配符。

使用CHARINDEX：

SELECT DISTINCT o.name AS Object_Name,o.type_descFROM sys.sql_modules mINNER JOIN sys.objects  oON m.object_id=o.object_idWHERE CHARINDEX('[ABD]',m.definition) >0 ;

使用PATINDEX：

SELECT DISTINCT o.name AS Object_Name,o.type_descFROM sys.sql_modules mINNER JOIN sys.objects  oON m.object_id=o.object_idWHERE PATINDEX('[[]ABD]',m.definition) >0 ;

使用这个双[[]ABD]类似于转义：

WHERE m.definition LIKE '%[[]ABD]%'

您也可以使用：

SELECT OBJECT_NAME(id)FROM syscommentsWHERE [text] LIKE '%flags.%'AND OBJECTPROPERTY(id, 'IsProcedure') = 1GROUP BY OBJECT_NAME(id)

包括评论

select top 10 * fromsys.procedureswhere object_definition(object_id) like '%\[ABD\]%'

试试这个请求：

查询

SELECT nameFROM   sys.proceduresWHERE  Object_definition(object_id) LIKE '%strHell%'

您是否尝试过使用一些第三方工具进行搜索？有几个是免费的，这在过去为我节省了大量时间。

下面是我成功使用的两个SSMS Addins。

apexsql搜索-在数据库中搜索模式和数据，并具有附加功能，例如依赖跟踪等。

SSMS工具包-具有与前一个相同的搜索功能和其他几个很酷的功能。SQLServer 2012不是免费的，但仍然非常实惠。

我知道这个答案不是100%与问题相关（更具体），但希望其他人会发现这很有用。

你也可以使用这个：

SELECT *FROM INFORMATION_SCHEMA.ROUTINESWHERE ROUTINE_DEFINITION like '%Search_String%'

Redgate的SQL搜索是一个很好的工具，它是一个免费的SSMS插件。

您也可以使用

CREATE PROCEDURE [Search](@Filter nvarchar(max))ASBEGIN
SELECT nameFROM   proceduresWHERE   definition LIKE '%'+@Filter+'%'
END

然后跑掉

exec [Search] 'text'

SELECT DISTINCTo.name AS Object_Name,o.type_descFROM sys.sql_modules m        INNER JOIN        sys.objects oON m.object_id = o.object_id WHERE m.definition Like '%[String]%';

它可能会帮助你！

SELECT DISTINCTA.NAME AS OBJECT_NAME,A.TYPE_DESCFROM SYS.SQL_MODULES MINNER JOIN SYS.OBJECTS A ON M.OBJECT_ID = A.OBJECT_IDWHERE M.DEFINITION LIKE '%['+@SEARCH_TEXT+']%'ORDER BY TYPE_DESC

/*SEARCH SPROCS & VIEWS
The following query will allow search within the definitionsof stored procedures and views.
It spits out the results as XML, with the full definitions,so you can browse them without having to script them individually.
*/
/*STEP 1: POPULATE SEARCH KEYS. (Set to NULL to ignore)*/DECLARE@def_key varchar(128) = '%foo%',      /* <<< definition search key */@name_key varchar(128) = '%bar%',     /* <<< name search key       */@schema_key varchar(128) = 'dbo';     /* <<< schema search key     */
;WITH SearchResults AS (/*STEP 2: DEFINE SEARCH QUERY AS CTE (Common Table Expression)*/SELECT[Object].object_id                       AS [object_id],[Schema].name                            AS [schema_name],[Object].name                            AS [object_name],[Object].type                            AS [object_type],[Object].type_desc                       AS [object_type_desc],[Details].definition                     AS [module_definition]FROM/* sys.sql_modules = where the body of sprocs and views live */sys.sql_modules AS [Details] WITH (NOLOCK)JOIN/* sys.objects = where the metadata for every object in the database lives */sys.objects AS [Object] WITH (NOLOCK) ON [Details].object_id = [Object].object_idJOIN/* sys.schemas = where the schemas in the datatabase live */sys.schemas AS [Schema] WITH (NOLOCK) ON [Object].schema_id = [Schema].schema_idWHERE(@def_key IS NULL OR [Details].definition LIKE @def_key)      /* <<< searches definition */AND (@name_key IS NULL OR [Object].name LIKE @name_key)       /* <<< searches name       */AND (@schema_key IS NULL OR [Schema].name LIKE @schema_key)   /* <<< searches schema     */)/*STEP 3: SELECT FROM CTE INTO XML*/
/*This outer select wraps the inner queries in to the <sql_object> root element*/SELECT(/*This inner query maps stored procedure rows to <procedure> elements*/SELECT TOP 100 PERCENT[object_id]                            AS [@object_id],[schema_name] + '.' + [object_name]    AS [@full_name],[module_definition]                    AS [module_definition]FROMSearchResultsWHEREobject_type = 'P'ORDER BY[schema_name], [object_name]FOR XMLPATH ('procedure'), TYPE) AS [procedures],  /* <<< as part of the outer query,this alias causes the <procedure> elementsto be wrapped within the <procedures> element */(/*This inner query maps view rows to <view> elements*/SELECT TOP 100 PERCENT[object_id]                            AS [@object_id],[schema_name] + '.' + [object_name]    AS [@full_name],[module_definition]                    AS [module_definition]FROMSearchResultsWHEREobject_type = 'V'ORDER BY[schema_name], [object_name]FOR XMLPATH ('view'), TYPE) AS [views]  /* <<< as part of the outer query,this alias causes the <view> elementsto be wrapped within the <views> element */FOR XMLPATH ('sql_objects')

使用SQL服务器的良好做法。

创建下面的存储过程并设置短键，

CREATE PROCEDURE [dbo].[Searchinall](@strFind AS VARCHAR(MAX))ASBEGINSET NOCOUNT ON;--TO FIND STRING IN ALL PROCEDURESBEGINSELECT OBJECT_NAME(OBJECT_ID) SP_Name,OBJECT_DEFINITION(OBJECT_ID) SP_DefinitionFROM   sys.proceduresWHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'END
--TO FIND STRING IN ALL VIEWSBEGINSELECT OBJECT_NAME(OBJECT_ID) View_Name,OBJECT_DEFINITION(OBJECT_ID) View_DefinitionFROM   sys.viewsWHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'END
--TO FIND STRING IN ALL FUNCTIONBEGINSELECT ROUTINE_NAME           Function_Name,ROUTINE_DEFINITION     Function_definitionFROM   INFORMATION_SCHEMA.ROUTINESWHERE  ROUTINE_DEFINITION LIKE '%'+@strFind+'%'AND ROUTINE_TYPE = 'FUNCTION'ORDER BYROUTINE_NAMEEND
--TO FIND STRING IN ALL TABLES OF DATABASE.BEGINSELECT t.name      AS Table_Name,c.name      AS COLUMN_NAMEFROM   sys.tables  AS tINNER JOIN sys.columns cON  t.OBJECT_ID = c.OBJECT_IDWHERE  c.name LIKE '%'+@strFind+'%'ORDER BYTable_NameENDEND

现在-设置短键如下，

因此，下次无论何时您想在Store procedure、Views、Functions和Tables等四个对象中的任何一个中查找特定文本。您只需要写下该关键字并按下快捷键。

例如：我想搜索“PaymentTable”，然后写“PaymentTable”，并确保您在查询编辑器中选择或突出显示所写的关键字，然后按快捷键ctrl+4-它会为您提供完整的结果。

Select distinct OBJECT_NAME(id) from syscomments where text like '%string%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1

我经常使用这个脚本来弄清楚要修改哪些进程，或者弄清楚什么使用表的一列，或者该表删除一些旧的垃圾。它检查由出色提供的sp_msforeachdb运行的实例上的每个数据库。

if object_id('tempdb..##nothing') is not nulldrop table ##nothing
CREATE TABLE ##nothing(DatabaseName varchar(30),SchemaName varchar(30),ObjectName varchar(100),ObjectType varchar(50))
EXEC master.sys.sp_msforeachdb'USE ?insert into ##nothingSELECTdb_name() AS [Database],[Scehma]=schema_name(o.schema_id),o.Name,o.typeFROM sys.sql_modules mINNER JOIN sys.objects oON o.object_id = m.object_idWHEREm.definition like ''%SOME_TEXT%'''--edit this text
SELECT * FROM ##nothing norder by OBJECTname

请将此视为“肮脏”的替代方案，但这救了我很多次，特别是当我不熟悉数据库项目时。有时你试图在所有SP中搜索一个字符串，而忘记了一些相关的逻辑可能是隐藏在函数和触发器之间，或者它可以简单地与你想象的措辞不同。

从您的MSSMS中，您可以右键单击您的数据库并选择Tasks -> Generate Scripts向导将所有SP、Fns和触发器输出到一个. sql文件中。

确保也选择触发器！

然后只需使用Sublime或记事本搜索您需要查找的字符串。我知道这可能是非常低效和偏执的方法，但它有效：）

 SELECT DISTINCT OBJECT_NAME([id]),[text]
FROM syscomments
WHERE [id] IN (SELECT [id] FROM sysobjects WHERE xtype IN
('TF','FN','V','P') AND status >= 0) AND
([text] LIKE '%text to be search%' )

OBJECT_NAME（[id]) --> 对象名称（视图，存储过程，标量函数，表函数名称）

id（int）=对象标识号

xtype char（2）对象类型。可以是以下对象类型之一：

FN=标量函数

P=存储过程

v=查看

TF=表函数

-- Applicable for SQL 2005+USE YOUR_DATABASE_NAME //;GO
SELECT [Scehma] = schema_name(o.schema_id),o.NAME,o.typeFROM sys.sql_modules mINNER JOIN sys.objects o ON o.object_id = m.object_idWHERE m.DEFINITION LIKE '%YOUR SEARCH KEYWORDS%'GO

我创建了一个过程来搜索过程/函数、表、视图或作业中的文本。第一个参数@search是搜索条件，@目标搜索目标，即过程、表等。如果未指定，则搜索全部。@db是指定要搜索的数据库，默认为当前数据库。这是我在动态SQL中的查询。

ALTER PROCEDURE [dbo].[usp_find_objects](@search VARCHAR(255),@target VARCHAR(255) = NULL,@db VARCHAR(35) = NULL)AS
SET NOCOUNT ON;
DECLARE @TSQL NVARCHAR(MAX), @USEDB NVARCHAR(50)
IF @db <> '' SET @USEDB = 'USE ' + @dbELSE SET @USEDB = ''
IF @target IS NULL SET @target = ''
SET @TSQL = @USEDB + '
DECLARE @search VARCHAR(128)DECLARE @target VARCHAR(128)
SET @search = ''%' + @search + '%''SET @target = ''' + @target + '''
IF @target LIKE ''%Procedure%'' BEGINSELECT o.name As ''Stored Procedures''FROM SYSOBJECTS oINNER JOIN SYSCOMMENTS c ON o.id = c.idWHERE c.text LIKE @searchAND o.xtype IN (''P'',''FN'')GROUP BY o.nameORDER BY o.nameEND
ELSE IF @target LIKE ''%View%'' BEGINSELECT o.name As ''Views''FROM SYSOBJECTS oINNER JOIN SYSCOMMENTS c ON o.id = c.idWHERE c.text LIKE @searchAND o.xtype = ''V''GROUP BY o.nameORDER BY o.nameEND
/* Table - search table name only, need to add column name */ELSE IF @target LIKE ''%Table%'' BEGINSELECT t.name AS ''TableName''FROM sys.columns cJOIN sys.tables t ON c.object_id = t.object_idWHERE c.name LIKE @searchORDER BY TableNameEND
ELSE IF @target LIKE ''%Job%'' BEGINSELECT  j.job_id,s.srvname,j.name,js.step_id,js.command,j.enabledFROM    [msdb].dbo.sysjobs jJOIN    [msdb].dbo.sysjobsteps jsON  js.job_id = j.job_idJOIN    master.dbo.sysservers sON  s.srvid = j.originating_server_idWHERE   js.command LIKE @searchEND
ELSE BEGINSELECT o.name As ''Stored Procedures''FROM SYSOBJECTS oINNER JOIN SYSCOMMENTS c ON o.id = c.idWHERE c.text LIKE @searchAND o.xtype IN (''P'',''FN'')GROUP BY o.nameORDER BY o.name
SELECT o.name As ''Views''FROM SYSOBJECTS oINNER JOIN SYSCOMMENTS c ON o.id = c.idWHERE c.text LIKE @searchAND o.xtype = ''V''GROUP BY o.nameORDER BY o.name
SELECT t.name AS ''Tables''FROM sys.columns cJOIN sys.tables t ON c.object_id = t.object_idWHERE c.name LIKE @searchORDER BY Tables
SELECT  j.name AS ''Jobs''FROM    [msdb].dbo.sysjobs jJOIN    [msdb].dbo.sysjobsteps jsON  js.job_id = j.job_idJOIN    master.dbo.sysservers sON  s.srvid = j.originating_server_idWHERE   js.command LIKE @searchEND'
EXECUTE sp_executesql @TSQL

更新：如果您重命名了一个过程，它只更新sysobjects而不更新syscomments，它保留了旧名称，因此除非您删除并重新创建该过程，否则该过程不会包含在搜索结果中。

此查询是从所有数据库中搜索存储过程中的文本。

DECLARE @T_Find_Text VARCHAR(1000) = 'Foo'
IF OBJECT_ID('tempdb..#T_DBNAME') IS NOT NULL DROP TABLE #T_DBNAMEIF OBJECT_ID('tempdb..#T_PROCEDURE') IS NOT NULL DROP TABLE #T_PROCEDURE
CREATE TABLE #T_DBNAME(IDX int IDENTITY(1,1) PRIMARY KEY, DBName VARCHAR(255))
CREATE TABLE #T_PROCEDURE(IDX int IDENTITY(1,1) PRIMARY KEY, DBName VARCHAR(255), Procedure_Name VARCHAR(MAX), Procedure_Description VARCHAR(MAX))
INSERT INTO #T_DBNAME (DBName)SELECT name FROM master.dbo.sysdatabases
DECLARE @T_C_IDX INT = 0DECLARE @T_C_DBName VARCHAR(255)DECLARE @T_SQL NVARCHAR(MAX)DECLARE @T_SQL_PARAM NVARCHAR(MAX)
SET @T_SQL_PARAM ='   @T_C_DBName VARCHAR(255), @T_Find_Text VARCHAR(255)'

WHILE EXISTS(SELECT TOP 1 IDX FROM #T_DBNAME WHERE IDX > @T_C_IDX ORDER BY IDX ASC)BEGIN
SELECT TOP 1@T_C_DBName = DBNameFROM #T_DBNAME WHERE IDX > @T_C_IDX ORDER BY IDX ASC
SET @T_SQL = ''
SET @T_SQL = @T_SQL + 'INSERT INTO #T_PROCEDURE(DBName, Procedure_Name, Procedure_Description)'SET @T_SQL = @T_SQL + 'SELECT SPECIFIC_CATALOG, ROUTINE_NAME, ROUTINE_DEFINITION 'SET @T_SQL = @T_SQL + 'FROM ' + @T_C_DBName +  '.INFORMATION_SCHEMA.ROUTINES  'SET @T_SQL = @T_SQL + 'WHERE ROUTINE_DEFINITION LIKE ''%''+ @T_Find_Text + ''%'' 'SET @T_SQL = @T_SQL + 'AND ROUTINE_TYPE = ''PROCEDURE'' '
BEGIN TRYEXEC SP_EXECUTESQL  @T_SQL, @T_SQL_PARAM, @T_C_DBName, @T_Find_TextEND TRYBEGIN CATCHSELECT @T_C_DBName + ' ERROR'END CATCH
SET @T_C_IDX = @T_C_IDX + 1END
SELECT IDX, DBName, Procedure_Name FROM #T_PROCEDURE ORDER BY DBName ASC

也试试这个：

   SELECT ROUTINE_NAMEFROM INFORMATION_SCHEMA.ROUTINESWHERE ROUTINE_DEFINITION like '%\[ABD\]%'

不同的版本，使查询更适合不同的编码实践。

SELECT DISTINCTO.NAME AS OBJECT_NAME,O.TYPE_DESCFROM SYS.SQL_MODULES MINNER JOINSYS.OBJECTS OON M.OBJECT_ID = O.OBJECT_IDWHERE UPPER(M.DEFINITION) LIKE UPPER('%Your Text%');

SELECT name , type_desc , create_date , modify_dateFROM   sys.proceduresWHERE  Object_definition(object_id) LIKE '%High%'

这个搜索例程基于https://stackoverflow.com/a/33631029/2735286，并且在搜索结果中也包含模式名称：

CREATE PROCEDURE [dbo].[Searchinall] (@strFind AS VARCHAR(MAX))ASBEGINSET NOCOUNT ON;--TO FIND STRING IN ALL PROCEDURESBEGINSELECT s.name SP_Schema_Name, OBJECT_NAME(p.OBJECT_ID) SP_Name,OBJECT_DEFINITION(p.OBJECT_ID) SP_DefinitionFROM   sys.procedures pINNER JOIN sys.schemas s on p.schema_id = s.schema_idWHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'END
--TO FIND STRING IN ALL VIEWSBEGINSELECT s.name SP_Schema_Name, OBJECT_NAME(OBJECT_ID) View_Name,OBJECT_DEFINITION(OBJECT_ID) View_DefinitionFROM   sys.views vINNER JOIN sys.schemas s on v.schema_id = s.schema_idWHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'END
--TO FIND STRING IN ALL FUNCTIONBEGINSELECT ROUTINE_SCHEMA, ROUTINE_NAME           Function_Name,ROUTINE_DEFINITION     Function_definitionFROM   INFORMATION_SCHEMA.ROUTINESWHERE  ROUTINE_DEFINITION LIKE '%'+@strFind+'%'AND ROUTINE_TYPE = 'FUNCTION'ORDER BYROUTINE_NAMEEND
--TO FIND STRING IN ALL TABLES OF DATABASE.BEGIN
SELECT s.name SP_Schema_Name, t.name      AS Table_Name,c.name      AS COLUMN_NAMEFROM   sys.tables  AS tINNER JOIN sys.columns cON  t.OBJECT_ID = c.OBJECT_IDINNER JOIN sys.schemas s on t.schema_id = s.schema_idWHERE  c.name LIKE '%'+@strFind+'%'ORDER BYTable_NameENDEND

以下是你如何使用它：

execute [dbo].[Searchinall] 'cust'