Search text in stored procedure in SQL Server

Question

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?

Does this answer your question? How do I find a stored procedure containing <text>? — Venkataraman R
– Venkataraman R, Commented Mar 12, 2021 at 8:56
Someday a whole week may go by when I don't search for and use this script. This week was not that week. — John Joseph
– John Joseph, Commented Aug 28, 2023 at 18:31

Mahmoud Gamal · Accepted Answer · 2020-12-16 10:06:45Z

719

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.

edited Dec 16, 2020 at 10:06

answered Feb 5, 2013 at 9:33

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Imad Over a year ago

doen't give schema name :(

patricus Over a year ago

@Imad You can get the schema name by adding SCHEMA_NAME(o.schema_id) AS Schema_Name to the select clause.

Charles Graham Over a year ago

This doesn't work with longer stored procedures. Use Ullas's SQL below instead.

Hunter Nelson Over a year ago

I found out the hard way that this answer will only search the first 4000 characters of stored procedure text. Check out this link for the real answer. sqlhints.com/2011/10/01/…

Eddie Kumar Over a year ago

Same here, information_schema didn't search content-text (within SProcs), instead use of "sys.procedures" worked for me. I understand that Microsoft recommends the use of information_schema, however apparently this is not working for many, hence Microsoft should look into this issue.

|

Brian · Accepted Answer · 2016-12-16 14:28:19Z

457

Try this request:

Query

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

edited Dec 16, 2016 at 14:28

Brian

25.9k18 gold badges87 silver badges179 bronze badges

answered Aug 21, 2013 at 14:39

user27332

4,7091 gold badge14 silver badges2 bronze badges

3 Comments

jazzabeanie Over a year ago

I had to run as admin for this work for me.

Trent Tamura Over a year ago

Does this option have the 4000 character limit on it?

user1535776 Over a year ago

Protip you need to be using the database you are searching before you run this. Also YES this works for procedures longer than 4000 characters. I even created a stored procedure with well over a 4000 character count, with a query string at the end, and it turned up in the result. THIS ONE RIGHT HERE IS WHAT YOU NEED TO USE.

Daniel Kelley · Accepted Answer · 2013-02-05 09:33:10Z

71

I usually run the following to achieve that:

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

answered Feb 5, 2013 at 9:33

Daniel Kelley

7,7677 gold badges45 silver badges51 bronze badges

3 Comments

Matthew Lock Over a year ago

this is good as it not only searches Stored Procs but also views and definitions of tables etc

JSON Over a year ago

this is the correct anwser

pratapsingh845 Over a year ago

@Daniel Kelley this is the exact answer, thanks

David Smithers · Accepted Answer · 2013-09-05 10:55:19Z

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.

Pedram · Accepted Answer · 2020-05-12 04:20:39Z

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.

In SSMS v18.2 (connected to Azure) the code works great. The short key returns Procedure or function 'Searchinall' expects parameter '@strFind', which was not supplied Any ideas?
@gordon613 have you typed keyword for searching while pressing short cut key on SSMS? e.g. 'Employee' and then ctrl + 4
@pedram.- thanks! I have now got it working - on my setup you need to type the keyword, then highlight it, and then press CTRL+4
@AngelWarrior, You might have tried it in new query and it should have worked for you. I mean no need to restart the SSMS. Thanks for providing Shortcuts path for SSMS 18.2.
One that is always missing is search through SQL job steps. I have seen quite few cases when that search was not run and we kept looking for something that was updating our data outside of all objects your query covers.

Milan · Accepted Answer · 2017-01-24 19:36:00Z

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

Kamil Budziewski · Accepted Answer · 2013-10-04 12:44:35Z

14

You can also use this one:

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

edited Oct 4, 2013 at 12:44

Kamil Budziewski

23.1k14 gold badges88 silver badges107 bronze badges

answered Oct 4, 2013 at 12:28

user2846553

1971 silver badge5 bronze badges

1 Comment

Shannon Severance Over a year ago

-1 Downside compared to other options is INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION only holds first 4000 characters of the routine.

Thavas Antonio · Accepted Answer · 2021-06-02 01:03:29Z

11

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

edited Jun 2, 2021 at 1:03

Thavas Antonio

5,9091 gold badge19 silver badges51 bronze badges

answered Jun 1, 2021 at 17:02

WaqarTahir

1491 silver badge2 bronze badges

Comments

Maddy · Accepted Answer · 2021-03-12 08:38:13Z

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

user1001101 · Accepted Answer · 2013-05-22 07:42:34Z

8

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

answered May 22, 2013 at 7:42

user1001101

1721 gold badge3 silver badges8 bronze badges

Comments

Martijn Pieters · Accepted Answer · 2015-07-16 11:03:56Z

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

fedorqui · Accepted Answer · 2013-03-26 16:42:40Z

7

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

edited Mar 26, 2013 at 16:42

fedorqui

293k112 gold badges592 silver badges640 bronze badges

answered Mar 26, 2013 at 16:25

Daniel RG

871 bronze badge

1 Comment

jlnorsworthy Over a year ago

You shouldn't use syscomments for this purpose. The text field cuts off at 4000 characters. The definition field from sys.sql_modules seems to store the entire text (more than 4000 anyway)

chollida · Accepted Answer · 2015-03-24 14:44:42Z

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

Atahan Ceylan · Accepted Answer · 2019-12-08 11:23:41Z

 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

Weihui Guo · Accepted Answer · 2020-02-23 23:43:01Z

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.

bonCodigo · Accepted Answer · 2013-02-05 09:41:12Z

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]%'

A.D. · Accepted Answer · 2017-11-04 11:27:15Z

also try this :

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

gil.fernandes · Accepted Answer · 2023-01-18 15:04:53Z

This search routine is based on https://stackoverflow.com/a/33631029/2735286 and contains the schema name too in the search results:

CREATE PROCEDURE [dbo].[Searchinall] (@strFind AS VARCHAR(MAX)) AS BEGIN SET NOCOUNT ON; --TO FIND STRING IN ALL PROCEDURES BEGIN SELECT s.name SP_Schema_Name, OBJECT_NAME(p.OBJECT_ID) SP_Name ,OBJECT_DEFINITION(p.OBJECT_ID) SP_Definition FROM sys.procedures p INNER JOIN sys.schemas s on p.schema_id = s.schema_id WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%' END --TO FIND STRING IN ALL VIEWS BEGIN SELECT s.name SP_Schema_Name, OBJECT_NAME(OBJECT_ID) View_Name ,OBJECT_DEFINITION(OBJECT_ID) View_Definition FROM sys.views v INNER JOIN sys.schemas s on v.schema_id = s.schema_id WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%' END --TO FIND STRING IN ALL FUNCTION BEGIN SELECT ROUTINE_SCHEMA, 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 s.name SP_Schema_Name, 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 INNER JOIN sys.schemas s on t.schema_id = s.schema_id WHERE c.name LIKE '%'+@strFind+'%' ORDER BY Table_Name END END

Here is how you use it:

execute [dbo].[Searchinall] 'cust'

Here is an alternative which lists all objects with a specific keyword in one single query:

SELECT DISTINCT s.name AS Schema_Name, 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 INNER JOIN sys.schemas s on o.schema_id = s.schema_id WHERE m.definition Like '%dim_forex%' ESCAPE '\' order by 3;

This query should return the same results and the procedure call in this answer.

Nokcha · Accepted Answer · 2017-10-13 02:22:32Z

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

Gopakumar N.Kurup · Accepted Answer · 2015-11-26 05:33:18Z

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

groggyjava · Accepted Answer · 2015-07-02 16:32:12Z

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

jesterm · Accepted Answer · 2016-12-07 19:43:36Z

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

Abhinav Sharma · Accepted Answer · 2017-07-23 14:06:36Z

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

Alex Nolasco · Accepted Answer · 2024-07-16 15:32:49Z

If you are in a hurry and are limited to MSSQL Management Studio, make use of FOR XML to see the body of the stored procedure within Management Studio.

SELECT OBJECT_NAME(object_id) as Name, ( SELECT OBJECT_DEFINITION(object_id) AS Body FOR XML PATH(''), TYPE ) AS Body FROM sys.all_objects WHERE OBJECT_DEFINITION(object_id) LIKE '%Some_Members%' ORDER BY Name;

Kamruzzaman · Accepted Answer · 2015-05-08 02:09:29Z

-2

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'

edited May 8, 2015 at 2:09

answered Sep 25, 2014 at 13:35

Kamruzzaman

1,4431 gold badge12 silver badges14 bronze badges

1 Comment

Roger Willcocks Over a year ago

The WHERE clause is missing a field reference. You should also reference the schema (sys)

Collectives™ on Stack Overflow

Search text in stored procedure in SQL Server

25 Answers 25

9 Comments

3 Comments

3 Comments

Comments

7 Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

25 Answers 25

9 Comments

3 Comments

3 Comments

Comments

7 Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

1 Comment

Linked

Related