Search All Stored Procedures For Text

Searching all stored procedures for a specific piece of text is a common task for database developers and administrators who need to locate references to a table, column, or business rule hidden inside complex code. Whether you are refactoring a schema, auditing for security issues, or simply trying to understand where a particular value is used, knowing how to search all stored procedures for text can save hours of manual inspection Turns out it matters..

Understanding Stored Procedures

Stored procedures are pre‑compiled database programs that encapsulate a set of SQL statements. They can include loops, conditional logic, and calls to other procedures, making them powerful but also opaque. Because the definition of a procedure is stored as text in the database catalog, you can query that text directly to find patterns, keywords, or even specific column names Surprisingly effective..

Why You Might Need to Search All Stored Procedures for Text

• Refactoring – When you rename a table or column, you must update every reference inside procedures.
• Security Audits – Locate potentially dangerous commands such as EXEC or xp_cmdshell.
• Compliance – Find hardcoded values (e.g., credit‑card numbers) that should be parameterized.
• Performance Tuning – Identify procedures that repeatedly query large tables without proper indexing.
• Documentation – Generate a map of which procedures depend on which objects.

General Approaches to Searching Procedure Definitions

1. Query System Views and Catalog Tables

Most relational database systems expose the source text of stored procedures through system views. By filtering these views, you can locate the exact line that contains your target text.

SQL Server

In SQL Server, the view sys.sql_modules contains the definition of each module, including stored procedures. A typical query looks like:

SELECT 
    OBJECT_NAME(object_id) AS ProcedureName,
    definition
FROM sys.sql_modules
WHERE definition LIKE '%YourSearchTerm%';

You can also join with sys.procedures to limit the result to procedures only It's one of those things that adds up..

MySQL

MySQL stores procedure definitions in the information_schema.ROUTINES table. Use the ROUTINE_DEFINITION column:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM information_schema.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%YourSearchTerm%'
  AND ROUTINE_TYPE = 'PROCEDURE';

PostgreSQL

PostgreSQL keeps the source of each function (including procedures) in the pg_proc system catalog. The column prosrc holds the body:

SELECT proname, prosrc
FROM pg_proc
WHERE prosrc ILIKE '%YourSearchTerm%';

Oracle

Oracle provides the DBA_SOURCE view, where each line of a procedure is stored as a separate row. You can aggregate lines or search directly:

### Oracle

Oracle stores each line of a procedure in the `DBA_SOURCE` (or `ALL_SOURCE` / `USER_SOURCE` for scoped access) view. Consider this: because the text is split into rows, you need to aggregate it before you can apply a `LIKE` predicate. The most straightforward way is to use `LISTAGG` (available in Oracle 12c and later) to concatenate the source lines and then search the resulting string.

```sql
/* Search for a keyword in all PL/SQL procedures owned by the current user */
SELECT owner,
       name   AS procedure_name,
       LISTAGG(text, ' ') WITHIN GROUP (ORDER BY line) AS source_code
FROM   user_source
WHERE  owner NOT IN ('SYS', 'OUTLN')               -- optional: ignore system-owned code
GROUP BY owner, name
HAVING LISTAGG(text, ' ') WITHIN GROUP (ORDER BY line) LIKE '%YourSearchTerm%';

If you need a case‑insensitive search, use the LOWER function on both sides:

HAVING LOWER(LISTAGG(text, ' ') WITHIN GROUP (ORDER BY line)) LIKE '%yoursearchterm%';

For older Oracle versions that lack LISTAGG, you can achieve the same result with CONNECT BY and WM_CONCAT:

SELECT owner,
       name   AS procedure_name,
       WM_CONCAT(text) AS source_code
FROM   user_source
WHERE  owner NOT IN ('SYS', 'OUTLN')
GROUP BY owner, name
HAVING WM_CONCAT(text) LIKE '%YourSearchTerm%';

Tip: If you only need to know where the term appears (line numbers), query user_source directly without aggregation:

SELECT line, text
FROM   user_source
WHERE  text LIKE '%YourSearchTerm%';

This approach is fast for a single object but becomes cumbersome when you must scan dozens of procedures.

Putting It All Together – A Cross‑Platform Search Pattern

Below is a generic, database‑agnostic template you can adapt to any of the major RDBMS platforms. It demonstrates how to:

1. Collect candidate objects (procedures only) from the appropriate catalog view.
2. Retrieve their source text using the platform‑specific column or aggregation method.
3. Filter on the search term (case‑insensitive where possible).
4. Return a unified result set that includes the object name, owner/schema, and the matching line(s).

/* -------------------------------------------------------------
   Unified search for a term across stored procedures
   ------------------------------------------------------------- */
WITH procedures AS (
  /* ---- SQL Server ---- */
  SELECT 'SQL Server' AS db_vendor,
         OBJECT_NAME(object_id) AS object_name,
         schema_name(schema_id) AS schema_name,
         definition AS source_text
  FROM   sys.sql_modules
  WHERE  OBJECTPROPERTY(object_id, 'IsProcedure') = 1

  UNION ALL

  /* ---- MySQL ---- */
  SELECT 'MySQL' AS db_vendor,
         routine_name AS object_name,
         routine_schema AS schema_name,
         routine_definition AS source_text
  FROM   information_schema.routines
  WHERE  routine_type = 'PROCEDURE'

  UNION ALL

  /* ---- PostgreSQL ---- */
  SELECT 'PostgreSQL' AS db_vendor,
         proname AS object_name,
         nspname AS schema_name,
         prosrc AS source_text
  FROM   pg_proc p
  JOIN   pg_namespace n ON n.oid = p.pronamespace
  WHERE  p.

  UNION ALL

  /* ---- Oracle ---- */
  SELECT 'Oracle' AS db_vendor,
         name AS object_name,
         owner AS schema_name,
         LISTAGG(text, ' ') WITHIN GROUP (ORDER BY line) AS source_text
  FROM   user_source
  WHERE  owner NOT IN ('SYS', 'OUTLN')
  GROUP BY owner, name
)
SELECT *
FROM   procedures
WHERE  LOWER(source_text) LIKE LOWER('%YourSearchTerm%')
ORDER BY db_vendor, schema_name, object_name;

How to use the script

• Replace 'YourSearchTerm' with the keyword, column name, or snippet you are hunting for.
• If you are searching a single database, you can drop the UNION ALL branches you don’t need, which reduces overhead.
• For environments that support dynamic SQL (e.g., SQL Server’s sp_executesql or Oracle’s DBMS_SQL), you can wrap the whole block in a procedure that iterates over a list of databases, making it easy to scan an entire instance or data‑warehouse estate.

Best Practices & Common Pitfalls

|