SQL Find Text In Stored Procedure: Complete Guide

Ever tried hunting down a piece of logic buried inside a dozen stored procedures, only to end up scrolling through pages of code like you’re looking for a needle in a haystack?
You’re not alone.

Most DBAs and developers have spent at least a few frantic minutes typing something like

SELECT * FROM sysobjects WHERE type = 'P' …

and then wondering why the result set looks like a random grocery list.
The short version is: SQL Server gives you a handful of built‑in tricks to search the text of stored procedures, but you have to know which ones actually work in practice.

Below is the one‑stop guide that covers everything you need to know about finding text in a stored procedure—whether you’re on SQL Server 2008, 2019, or the newest Azure‑SQL offering.

What Is “Finding Text in a Stored Procedure”?

When we talk about “finding text” we’re really talking about searching the definition of a procedure for a particular string.
Think of a stored procedure as a recipe stored in the database catalog.
If you want to know whether that recipe mentions “GETDATE()” or a specific table name, you need to query the catalog views that hold the source code.

In SQL Server the source lives in system tables like sys.objects.
Consider this: other platforms—Oracle, MySQL, PostgreSQL—store it in similar metadata tables, but the syntax differs. sql_modulesandsys.For the rest of this post I’ll focus on Microsoft SQL Server, because it’s the environment where most people ask “how do I find text in a stored procedure?

The Core Idea

Grab the definition of each procedure (the actual T‑SQL script).
Filter that definition with LIKE, CHARINDEX, or full‑text search.
Return the procedure name (and optionally the matching line).

That’s it. The rest is just making the query readable, fast, and flexible enough for real‑world use.

Why It Matters / Why People Care

You might wonder why anyone would bother searching stored procedures at all. Here are three scenarios that pop up all the time:

Refactoring a column name – The column CustomerID is being renamed to ClientID. You need every procedure that references the old name before you run the migration script. Miss one, and the app blows up in production.
Security audit – A compliance sweep asks you to locate any procedure that calls xp_cmdshell or accesses a privileged table. You can’t manually open each file; you need a reliable query.
Debugging a mysterious error – An error message mentions a table that you swear isn’t used anywhere. A quick text search across procedures tells you exactly where the offending call lives.

In practice, the difference between a quick catalog query and a manual code review can be hours versus days. And trust me, the hours add up fast when you’re juggling multiple databases.

How It Works (or How to Do It)

Below are the most common ways to search procedure text. I’ll start with the simplest LIKE approach, then show a more powerful PATINDEX/CHARINDEX combo, and finally a full‑text search for massive code bases.

### 1. Basic LIKE Search

SELECT
    o.name               AS ProcedureName,
    m.definition         AS ProcedureText
FROM sys.sql_modules AS m
JOIN sys.objects      AS o
  ON m.object_id = o.object_id
WHERE o.type = 'P'                     -- P = Stored Procedure
  AND m.definition LIKE '%YourSearchTerm%';

Why it works: sys.sql_modules.definition holds the exact T‑SQL script as an nvarchar(max). The LIKE operator does a case‑insensitive search by default (unless you have a case‑sensitive collation).

Things to watch out for

The search term must be surrounded by % wildcards, otherwise you’ll only get exact matches.
If your database uses a binary collation, you’ll need to add COLLATE Latin1_General_CI_AS to force case‑insensitivity.

### 2. Using CHARINDEX for Position

If you want to know where in the procedure the text appears, CHARINDEX returns the starting character position.

SELECT
    o.name,
    CHARINDEX('YourSearchTerm', m.definition) AS Position,
    SUBSTRING(m.definition,
              CHARINDEX('YourSearchTerm', m.definition) - 30,
              100) AS Snippet
FROM sys.sql_modules AS m
JOIN sys.objects AS o
  ON m.object_id = o.object_id
WHERE o.type = 'P'
  AND CHARINDEX('YourSearchTerm', m.definition) > 0;

What’s the benefit? You get a tiny snippet of code around the match, which is handy when you’re scanning dozens of procedures.

Tip: Wrap the CHARINDEX call in NULLIF(...,0) if you want to avoid a zero position showing up as a false positive.

### 3. PATINDEX for Pattern Matching

PATINDEX lets you use wildcards inside the pattern, something LIKE can’t do when you need to capture variable parts.

SELECT
    o.name,
    PATINDEX('%dbo.%TableName%', m.definition) AS PatternPos
FROM sys.sql_modules AS m
JOIN sys.objects AS o
  ON m.object_id = o.object_id
WHERE o.type = 'P'
  AND PATINDEX('%dbo.%TableName%', m.definition) > 0;

Here %dbo.%TableName% will match any schema that starts with dbo. followed by any characters, then TableName. It’s a quick way to hunt for loosely‑typed references.

### 4. Full‑Text Search on sys.sql_modules

When you have thousands of procedures, LIKE can become sluggish because it scans the whole definition column each time. Enabling full‑text search on sys.sql_modules (SQL Server 2012+ only) can speed things up dramatically.

Step 1 – Create a full‑text catalog (if you don’t have one):

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;

Step 2 – Create a full‑text index on the definition column:

CREATE FULLTEXT INDEX ON sys.sql_modules(definition)
KEY INDEX PK_sys_sql_modules
ON ftCatalog
WITH CHANGE_TRACKING AUTO;

Note: PK_sys_sql_modules is the primary key on sys.sql_modules. If you’re on Azure SQL Managed Instance, the catalog is already there.

Step 3 – Query with CONTAINS:

Continue exploring with our guides on yellow dairy product that adds richness and flavor and why is paragraph alignment important.

SELECT
    o.name,
    m.definition
FROM sys.sql_modules AS m
JOIN sys.objects AS o
  ON m.object_id = o.object_id
WHERE o.type = 'P'
  AND CONTAINS(m.definition, '"YourSearchTerm*"');

The asterisk works as a prefix wildcard, so "GetDate*" will match GETDATE(), GETDATE, etc. Full‑text search also respects stop‑words, so you won’t get false hits on common words like “the”.

### 5. Searching Across All Objects (Not Just Procedures)

Sometimes you need to include functions, triggers, or views. Just broaden the type filter:

WHERE o.type IN ('P','FN','IF','TF','TR','V')

That covers stored procedures (P), scalar functions (FN), inline table‑valued functions (IF), multi‑statement table‑valued functions (TF), triggers (TR), and views (V).

Common Mistakes / What Most People Get Wrong

Forgetting the object type filter – Running a LIKE query on sys.sql_modules without WHERE o.type = 'P' returns every object that has text, including system procedures you never touch. Your result set balloons, and you start doubting the tool.
Assuming case‑sensitivity – If your collation is case‑sensitive, LIKE '%getdate%' won’t match GETDATE(). The fix? Add COLLATE Latin1_General_CI_AS or switch to LOWER(m.definition) LIKE '%getdate%'.
Missing schema qualifiers – Searching for just TableName will also pull rows where the term appears in comments or as part of another identifier. Use a more precise pattern like '%dbo.TableName%' or combine with CHARINDEX to filter out comments.
Overlooking encrypted procedures – If a procedure is created with WITH ENCRYPTION, its definition is stored as binary and you can’t read it with the catalog views. The only workaround is to have the original script or use a third‑party decryption tool (legality varies).
Running the query on the wrong database – System views are scoped per database. If you connect to master and run the script, you’ll only see procedures that live in master. Double‑check USE YourDatabase; before you start.

Practical Tips / What Actually Works

Wrap the search term in a variable – Makes the query reusable.

DECLARE @SearchTerm NVARCHAR(200) = 'CustomerID';
SELECT o.name, m.definition
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON m.object_id = o.object_id
WHERE o.type = 'P'
  AND m.

Strip out comments – If you only care about executable code, filter out /* … */ and -- comments with a quick REPLACE chain. It’s not perfect but helps reduce noise.
```
SELECT o.name,
       REPLACE(REPLACE(m.definition, CHAR(13)+CHAR(10), ' '), '--', '') AS CleanDef
FROM …
```
Export results to a file – For large codebases, pipe the output to a CSV using SQLCMD or SSMS’s “Results to Text” and then grep locally.
Combine with sp_helptext – If you need the exact line numbers, use sp_helptext inside a loop.
```
DECLARE @proc sysname = 'dbo.usp_MyProc';
EXEC sp_helptext @proc;
```
Schedule a weekly audit – Create a SQL Agent job that runs the full‑text search for risky keywords (xp_cmdshell, OPENROWSET, EXEC sp_ etc.) and emails the results. It’s a cheap way to catch accidental privilege escalations.
put to work third‑party tools – Tools like Redgate’s SQL Search or ApexSQL Search provide a UI on top of the same catalog queries, plus cross‑database support. If you’re not comfortable writing the T‑SQL yourself, they’re worth a look.

FAQ

Q: Can I search for a phrase that spans multiple lines?
A: Yes. The definition column stores the whole script as a single string, including line breaks. Using LIKE '%FirstLine%SecondLine%' works, but make sure you include the line‑break characters (CHAR(13)+CHAR(10)) if you need an exact match.

Q: How do I search for a column name that might be quoted or bracketed?
A: Use a pattern that accounts for optional delimiters:

WHERE m.definition LIKE '%[[]CustomerID[]]%'   -- for [CustomerID]
   OR m.definition LIKE '%"CustomerID"%'
   OR m.definition LIKE '%CustomerID%'

Q: Does this work on Azure SQL Database?
A: Absolutely. Azure SQL supports sys.sql_modules and the same LIKE/CHARINDEX functions. Full‑text search is also available, but you need to enable it in the logical server first.

Q: What if the procedure is encrypted?
A: Encrypted procedures hide their definition from sys.sql_modules. You’ll only see a placeholder. The only way around it is to locate the original creation script or, if you own the code, drop and recreate without encryption.

Q: Is there a performance impact on production?
A: A simple LIKE on sys.sql_modules is lightweight because the catalog is small compared to user tables. Full‑text indexing adds some overhead on DDL changes, but the read‑side performance gain usually outweighs it for large environments.

Finding text inside stored procedures doesn’t have to be a chore.
With a few catalog queries, a dash of LIKE or CHARINDEX, and maybe a full‑text index for the heavy hitters, you can locate any piece of code in seconds.

Next time you’re asked to rename a column, audit a security risk, or just satisfy curiosity, fire off one of the snippets above and let SQL Server do the digging for you.

Happy searching!