TSQL Search Stored Procedures

TSQL-SearchStoredProcs.md

The following provides a couple of options on how to search MS SQL stored procedures for text. This can be useful if you're trying to find references to pieces of code or notes.

The following has been tested against MS SQL Server 2000-2016

Method 1

-- Search Stored Procedures Script
-- Input: Replace '%...%'  with you search string.  i.e. '%D_Request%'
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
  ROUTINE_TYPE = 'PROCEDURE'
  AND ROUTINE_DEFINITION like '%SEARCH TEXT HERE%'
ORDER BY ROUTINE_NAME;

Method 2

This will return back the Stored Procedure's via searching sys.sql_modules

-- Search Stored Procedures Script
-- Input: put your search text inside 'SEARCH TEXT HERE'
DECLARE @Search varchar(255)
SET @Search='SEARCH TEXT HERE'
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 '%'+@Search+'%'
ORDER BY 2,1;