Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below 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?

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.

Try this request:

Query

SELECT name
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%strHell%'

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.

ApexSQL Search – Searches both schema and data in databases and has additional features such as dependency tracking and more…

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.

I know this answer is not 100% related to the questions (which was more specific) but hopefully others will find this useful.

I usually run the following to achieve that:

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

Good practice to work with 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, Functions and Tables. You just need to write that keyword and press shortcut key.

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.

Redgate's SQL Search is a great tool for doing this, it's a free plugin for SSMS.

Please take this as a "dirty" alternative but 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 Triggers or it can be simply worded differently than you thought.

From your MSSMS you may right click your DB and select Tasks -> Generate Scripts wizard to output all the SPs, Fns and Triggers into a single .sql file.

Make sure to select Triggers too!

Then just use Sublime or Notepad to search for the string you need to find. I know this may be quite inefficient and paranoid approach but it works :)

You can also use this one:

SELECT * 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION like '%Search_String%'

select top 10 * from
sys.procedures
where object_definition(object_id) like '%\[ABD\]%'

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

Also you can use:

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

Thats include comments

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 '%[String]%';

 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]) --> Object Name (View,Store Procedure,Scalar Function,Table function name)

id (int) = Object identification number

xtype char(2) Object type. Can be one of the following object types:

FN = Scalar function

P = Stored procedure

V = View

TF = Table function

I created a procedure to search text in procedures/functions, tables, views, or jobs. The first parameter @search is the search criterion, @target the search target, i.e., procedures, tables, etc. If not specified, search all. @db is to specify the database to search, default to your current database. Here is my query in dynamic 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 ' + @db
ELSE 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%'' BEGIN
	SELECT o.name As ''Stored Procedures''
	FROM SYSOBJECTS o 
	INNER JOIN SYSCOMMENTS c ON o.id = c.id
	WHERE c.text LIKE @search
		AND o.xtype IN (''P'',''FN'')
	GROUP BY o.name
	ORDER BY o.name
END

ELSE IF @target LIKE ''%View%'' BEGIN
	SELECT o.name As ''Views''
	FROM SYSOBJECTS o 
	INNER JOIN SYSCOMMENTS c ON o.id = c.id
	WHERE c.text LIKE @search
		AND o.xtype = ''V''
	GROUP BY o.name
	ORDER BY o.name
END

/* Table - search table name only, need to add column name */
ELSE IF @target LIKE ''%Table%'' BEGIN
	SELECT t.name AS ''TableName''
	FROM sys.columns c 
	JOIN sys.tables t ON c.object_id = t.object_id
	WHERE c.name LIKE @search
	ORDER BY TableName
END

ELSE IF @target LIKE ''%Job%'' BEGIN
	SELECT	j.job_id,
		s.srvname,
		j.name,
		js.step_id,
		js.command,
		j.enabled 
	FROM	[msdb].dbo.sysjobs j
	JOIN	[msdb].dbo.sysjobsteps js
		ON	js.job_id = j.job_id 
	JOIN	master.dbo.sysservers s
		ON	s.srvid = j.originating_server_id
	WHERE	js.command LIKE @search
END

ELSE BEGIN 
	SELECT o.name As ''Stored Procedures''
	FROM SYSOBJECTS o 
	INNER JOIN SYSCOMMENTS c ON o.id = c.id
	WHERE c.text LIKE @search
		AND o.xtype IN (''P'',''FN'')
	GROUP BY o.name
	ORDER BY o.name

	SELECT o.name As ''Views''
	FROM SYSOBJECTS o 
	INNER JOIN SYSCOMMENTS c ON o.id = c.id
	WHERE c.text LIKE @search
		AND o.xtype = ''V''
	GROUP BY o.name
	ORDER BY o.name

	SELECT t.name AS ''Tables''
	FROM sys.columns c 
	JOIN sys.tables t ON c.object_id = t.object_id
	WHERE c.name LIKE @search
	ORDER BY Tables

	SELECT	j.name AS ''Jobs''
	FROM	[msdb].dbo.sysjobs j
	JOIN	[msdb].dbo.sysjobsteps js
		ON	js.job_id = j.job_id 
	JOIN	master.dbo.sysservers s
		ON	s.srvid = j.originating_server_id
	WHERE	js.command LIKE @search
END
'

EXECUTE sp_executesql @TSQL

>Update: If you renamed a procedure, it only updates sysobjects but not syscomments, which keeps the old name and therefore that procedure will not be included in the search result unless you drop and recreate the procedure.

SELECT name , type_desc , create_date , modify_date
FROM   sys.procedures 
WHERE  Object_definition(object_id) LIKE '%High%'

Using CHARINDEX:

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 CHARINDEX('[ABD]',m.definition) >0 ;

Using PATINDEX:

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 PATINDEX('[[]ABD]',m.definition) >0 ; 

Using this double [[]ABD] is similar to escaping :

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

also try this :

   SELECT ROUTINE_NAME 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION like '%\[ABD\]%'

This query is search text in stored procedure from all databases.

DECLARE @T_Find_Text VARCHAR(1000) = 'Foo'

IF OBJECT_ID('tempdb..#T_DBNAME') IS NOT NULL DROP TABLE #T_DBNAME
IF 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 = 0
DECLARE @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 = DBName 
	FROM #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 TRY
		EXEC SP_EXECUTESQL	@T_SQL, @T_SQL_PARAM, @T_C_DBName, @T_Find_Text
	END TRY
	BEGIN CATCH
		SELECT @T_C_DBName + ' ERROR'
	END CATCH

	SET @T_C_IDX = @T_C_IDX + 1
END

SELECT IDX, DBName, Procedure_Name FROM #T_PROCEDURE ORDER BY DBName ASC

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

A different version, To make query more appropriate for different coding practices.

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 UPPER(M.DEFINITION) LIKE UPPER('%Your Text%');

/* 
    SEARCH SPROCS & VIEWS
    
    The following query will allow search within the definitions 
    of 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_id
    JOIN 
        /* sys.schemas = where the schemas in the datatabase live */
        sys.schemas AS [Schema] WITH (NOLOCK) ON [Object].schema_id = [Schema].schema_id
    WHERE 
        (@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]
    FROM
        SearchResults
    WHERE
        object_type = 'P'
    ORDER BY
        [schema_name], [object_name]
    FOR XML
        PATH ('procedure'), TYPE
) AS [procedures],  /* <<< as part of the outer query, 
                           this alias causes the <procedure> elements
                           to 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]
    FROM
        SearchResults
    WHERE
        object_type = 'V'
    ORDER BY
        [schema_name], [object_name]
    FOR XML
        PATH ('view'), TYPE
) AS [views]  /* <<< as part of the outer query, 
                     this alias causes the <view> elements
                     to be wrapped within the <views> element */
FOR XML 
    PATH ('sql_objects')

Every so often I use this script to figure out which procs to modify, or to figure out what uses a column of a table, or that table at all to remove some old junk. It checks each database on the instance it is ran on by the wonderfully supplied sp_msforeachdb.

if object_id('tempdb..##nothing') is not null
	drop 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 ##nothing
SELECT 
db_name() AS [Database],
[Scehma]=schema_name(o.schema_id), 
o.Name, 
o.type 
FROM sys.sql_modules m
INNER JOIN sys.objects o
	ON o.object_id = m.object_id
WHERE 
	m.definition like ''%SOME_TEXT%'''  
--edit this text

SELECT * FROM ##nothing n
order by OBJECTname 

-- Applicable for SQL 2005+
USE YOUR_DATABASE_NAME //;
	GO

SELECT [Scehma] = schema_name(o.schema_id)
	,o.NAME
	,o.type
FROM sys.sql_modules m
INNER JOIN sys.objects o ON o.object_id = m.object_id
WHERE m.DEFINITION LIKE '%YOUR SEARCH KEYWORDS%'
GO

You can also use

CREATE PROCEDURE [Search](
	@Filter nvarchar(max)
)
AS
BEGIN

SELECT name
FROM   procedures
WHERE   definition LIKE '%'+@Filter+'%'

END

and then run

exec [Search] 'text'