# SQL Server: Search Text Inside Stored Procedures

> Search the text of every stored procedure in SQL Server using sys.procedures and OBJECT_DEFINITION, since INFORMATION_SCHEMA.ROUTINES truncates at 4000 characters

**URL:** https://sentry.io/answers/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:

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

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

```sql
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.

---

*Source: [sentry.io/answers/how-do-i-search-text-in-stored-procedures-in-sql-server/](https://sentry.io/answers/how-do-i-search-text-in-stored-procedures-in-sql-server/)*