How do I search text in stored procedures in SQL Server?

The Problem

In SQL Server, you might have hundreds of stored procedures. How do you search the text of all of them to discover which uses a table, column, or string that you want to find?

The Solution

Let’s create a sample stored procedure to demonstrate how to search its text:

CREATE PROCEDURE HelloWorld
AS
BEGIN
    PRINT 'Hello, [World!';
END;

To search all our stored procedures for [World, we can run:

SELECT
	OBJECT_NAME(object_id) as Name,
    OBJECT_DEFINITION(object_id) as Body
FROM
	sys.procedures
WHERE
	OBJECT_DEFINITION(object_id) LIKE '%[[]world%'

-- Name |           Body |
-- ----------------------------------+
-- HelloWorld|      CREATE PROCEDURE HelloWorld¶AS¶BEGIN¶    PRINT 'Hello, [World!';¶END;|

Note that to search for [ you have to enclose it in [[] because brackets usually encase table and column names in SQL Server. Depending on the text you are looking for you might want to try excluding the brackets, including them normally, or specifying your escape sequence explicitly with LIKE '%\[world%' ESCAPE '\'.

Do not try to search your stored procedures using INFORMATION_SCHEMA.ROUTINES. That searches only the first 4000 characters of the procedure. Rather use sys.procedures, as shown above.