Logo
Back

How to search text in stored procedure in SQL Server?

Searching for a specific text or keyword within the definition of stored procedures in SQL Server can be done by querying metadata tables like sys.sql_modules (preferred in modern SQL Server) or syscomments (deprecated, but still works in older versions). This approach helps you find references to table names, columns, variables, or any arbitrary string inside the body of your procedures.

Using sys.sql_modules

The sys.sql_modules catalog view stores the SQL definition of various programmable objects (stored procedures, functions, triggers, views). You can join it with sys.objects to filter by object type and name.

SELECT 
    obj.name AS [ProcedureName],
    mod.definition AS [ProcedureDefinition]
FROM sys.sql_modules AS mod
JOIN sys.objects AS obj
    ON mod.object_id = obj.object_id
WHERE mod.definition LIKE '%YourSearchText%'
  AND obj.type = 'P';  -- 'P' stands for stored procedure

Explanation

  1. sys.sql_modules: Contains one row per SQL object (stored procedures, views, etc.), with a column named definition holding the text.
  2. sys.objects: Identifies each object’s type (e.g., P = procedure, FN = scalar function, TF = table-valued function).
  3. LIKE '%YourSearchText%': Searches for your specified text in the stored procedure’s definition.
  4. obj.type = 'P': Restricts the query to procedures only.

Using syscomments (Deprecated)

In older SQL Server versions, you might see queries against syscomments (or sys.syscomments) to find text in procedure definitions. It still works but is no longer recommended. If necessary:

SELECT 
    o.name AS [ProcedureName],
    c.text AS [ProcedureDefinition]
FROM syscomments c
JOIN sysobjects o
    ON c.id = o.id
WHERE c.text LIKE '%YourSearchText%'
  AND o.xtype = 'P';

Caveats

  • syscomments splits large definitions into multiple rows. This can complicate searching since your text might span multiple rows.
  • Not advisable for new code; use sys.sql_modules whenever possible.

Practical Tips

  1. Use Wildcards Wisely

    • '%text%' to find partial matches anywhere.
    • 'text%' or '%text' if you know the exact start/end.

  2. Case Sensitivity

    • Dependent on the collation of your database or instance. If you’re on a case-sensitive collation and want to ignore case, consider COLLATE options or convert the columns to a specific case.

  3. Filtering by Schema/Name

    • If you only want to look at stored procedures with a certain name, add AND obj.name LIKE 'myProc%' or a similar condition.

  4. Security & Permissions

    • You need sufficient permissions to query system catalog views (VIEW DEFINITION or similar). If you can’t see definitions, ask your DBA to grant the necessary rights.

Summary

To search text within a stored procedure in SQL Server:

  • Use sys.sql_modules joined with sys.objects (the modern, recommended approach).
  • Filter on definition with LIKE '%YourSearchText%' and use obj.type = 'P' to limit results to stored procedures.
  • If working with older SQL Server versions or legacy code, you might see or need syscomments, though it is less reliable for large definitions.

This technique quickly locates references to specific keywords, table names, or variables across all your stored procedures, saving you time in debugging or refactoring.

Recommended Courses

  1. Grokking Database Fundamentals for Tech Interviews

  2. Relational Database Design and Modeling for Software Engineers

  3. Grokking System Design Fundamentals

CONTRIBUTOR
TechGrind