Search text in stored procedure in SQL Server

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

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

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

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

Try this request:

**Query**


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

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

[![enter image description here][1]][1]

Make sure to select Triggers too!

[![enter image description here][2]][2]

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


[1]:

[2]:

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

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

also try this :

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

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