SQL Server Search Stored Procedure and View Text

calendar Apr 9, 2026 / · 10 min read · sql server sql scripts sys.sql_modules sys.objects stored procedures views database administration object search maintenance scripts schema management  ·
Share on: linkedin copy

Before a column is renamed or a table is dropped, the question is which procedures, views, and functions still mention it. This script answers that by searching the stored text of every SQL module in the current database for a keyword, matching on the object name as well as the definition. It returns the schema, object name, object type, and the full definition of each match.

Purpose and Overview

A text search of module definitions finds references that no dependency tracking will ever show: a table named inside a dynamic SQL string, a column name in a comment, a hard-coded value that has to change with a business rule. sys.sql_modules holds that text. It returns "a row for each object that is a SQL language-defined module", and objects of type P (stored procedure), RF (replication-filter procedure), V (view), TR (trigger), FN (scalar function), IF (inline table-valued function), TF (table-valued function), and R (rule) have an associated SQL module. Stand-alone defaults, type D, also have a module definition in this view.

The definition column is nvarchar(max) and contains the "SQL text that defines this module". The same value can be obtained with the OBJECT_DEFINITION built-in function. One rule shapes every search: a NULL definition means the module is encrypted. An encrypted procedure still has a row in the view, but its text cannot be searched, so a clean result is silent about those objects.

The view has no names or schemas, only an object_id, so the script joins to sys.objects to get the object name, schema, and type. sys.objects contains a row for each user-defined, schema-scoped object created within a database. It does not show DDL triggers, because they are not schema-scoped, and that limit carries over to this script. A DML trigger appears; a database-level or server-level DDL trigger does not. The Practical Tips section covers how to include them.

There is a second reason to prefer a text search to the dependency views. sys.sql_expression_dependencies records a dependency only when one entity "appears by name in a persisted SQL expression of another entity", and dependency information is not created or maintained for rules, defaults, temporary tables, temporary stored procedures, or system objects. A search of sys.sql_modules.definition has no such blind spots for the object types it covers, at the price of false positives. When the search finds a match, the dependency view can help decide whether it is a real reference.

Code Breakdown

The script takes one search term and returns every module whose name or definition contains it. Run it in the database you want to search.

 1DECLARE @search_string NVARCHAR(256) = N'YourSearchTerm';
 2
 3-- Escape LIKE wildcards in the term so it is matched literally
 4DECLARE @pattern NVARCHAR(600) =
 5    N'%' + REPLACE(REPLACE(REPLACE(REPLACE(@search_string,
 6        N'\', N'\\'), N'%', N'\%'), N'_', N'\_'), N'[', N'\[') + N'%';
 7
 8SELECT
 9    DB_NAME()                       AS database_name,
10    SCHEMA_NAME(o.schema_id)        AS schema_name,
11    o.name                          AS object_name,
12    CASE o.type
13        WHEN 'P'  THEN 'Stored Procedure'
14        WHEN 'V'  THEN 'View'
15        WHEN 'FN' THEN 'Scalar Function'
16        WHEN 'IF' THEN 'Inline Table-Valued Function'
17        WHEN 'TF' THEN 'Table-Valued Function'
18        WHEN 'TR' THEN 'DML Trigger'
19        WHEN 'RF' THEN 'Replication Filter Procedure'
20        ELSE o.type_desc
21    END                             AS object_type,
22    m.definition                    AS object_definition
23FROM sys.sql_modules AS m
24INNER JOIN sys.objects AS o
25    ON m.object_id = o.object_id
26WHERE m.definition LIKE @pattern ESCAPE N'\'
27   OR o.name       LIKE @pattern ESCAPE N'\'
28ORDER BY o.type, o.name;

The search term and the escaped pattern

@search_string is NVARCHAR(256), so the comparison with the nvarchar(max) definition stays in Unicode. The LIKE operator treats %, _, and [ as wildcards. % matches any string of zero or more characters, _ any single character, and [ ] any single character within a range or set. A search for a name such as order_items would therefore also match orderXitems unless the underscore is escaped. The script prefixes each wildcard in the term with a backslash and tells LIKE to treat that backslash as the escape character through the ESCAPE clause. The escape character is one placed in front of a wildcard so it is "interpreted as a regular character and not as a wildcard", and it must evaluate to a single character. The backslash itself is doubled first, so a term that contains one stays intact. Drop the REPLACE calls if you do want wildcards in the term.

sys.sql_modules

The FROM clause reads sys.sql_modules. Two characteristics of its definition column matter for searching. The column is NULL for encrypted modules, as noted above, so those rows never match on text, though they can still match on name through the second LIKE. And renaming a stored procedure, function, view, or trigger "doesn't change the name of the corresponding object in the definition column", which is why dropping and recreating the object is recommended over sp_rename. After an sp_rename, a search for the new name finds nothing in the definition, while the old name still sits inside the CREATE text.

Joining sys.objects

sys.objects supplies the name, schema, and type. The join on m.object_id = o.object_id links each definition to its metadata, and SCHEMA_NAME(o.schema_id) converts the schema ID to a name, which matters when two schemas hold objects with the same name. The CASE expression turns the type codes from sys.objects into labels: P is a SQL stored procedure, V a view, FN a SQL scalar function, IF a SQL inline table-valued function, TF a SQL table-valued function, TR a SQL DML trigger, and RF a replication-filter procedure. The ELSE o.type_desc branch returns the description for anything else that has a module, such as rules and defaults.

The WHERE clause and ordering

The WHERE clause tests both m.definition and o.name. A procedure whose name contains the term is returned even when its body does not, which helps when you remember only half of a procedure name. ORDER BY o.type, o.name groups results by type and sorts by name within each group. Order by o.name alone for a flat list.

Key Benefits and Use Cases

  • Pre-change impact check. Find every module that mentions a table, column, or literal before you rename or drop it.
  • Dynamic SQL and comments included. Because the search reads the definition text, it finds names inside string literals that dependency tracking cannot see.
  • One query for several object types. Procedures, views, functions, and DML triggers come back in one result set with a type label.
  • Name search as well. Matching on o.name finds an object when you remember only part of its name.
  • Readable context. The full definition comes back with the match, so you can read the usage without opening each object in a separate query window.
  • Same query in any database. DB_NAME() and SCHEMA_NAME() make the output self-describing when you paste results from several databases together.

Performance Considerations

  • Text scan of every module. The query evaluates LIKE against the nvarchar(max) definition of each module, so cost grows with the number and size of procedures in the database. On a database with thousands of large modules it can take noticeably longer than a normal metadata query, so run it on demand, not from an application.
  • Encrypted modules are invisible to the text match. A NULL definition cannot match LIKE. Count the encrypted modules separately with WHERE definition IS NULL so you know how much of the database the search could not read.
  • Per-database scope. Each run reads one database. To search several, run the script in each, and use the database_name column to keep the results apart.
  • Permissions limit the rows. The visibility of metadata in catalog views is limited to securables that a user either owns or on which the user was granted some permission. A low-privilege login can get a clean result from a database that does contain matches. Run the search as an account with the access you intend to audit.
  • OBJECT_DEFINITION for one object. For a single known object, OBJECT_DEFINITION(OBJECT_ID(N'dbo.ProcedureName')) returns its source text. It returns NULL on error or when the caller lacks permission, and user object definitions are visible to the owner or to grantees with ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION. The catalog-view query is the right tool when you do not yet know which object you want.

Practical Tips

To limit results to stored procedures, add a type filter:

1WHERE (m.definition LIKE @pattern ESCAPE N'\'
2    OR o.name       LIKE @pattern ESCAPE N'\')
3  AND o.type = 'P'

To exclude objects created by internal SQL Server components, add the is_ms_shipped flag, defined as "Object is created by an internal SQL Server component":

1WHERE o.is_ms_shipped = 0
2  AND (m.definition LIKE @pattern ESCAPE N'\'
3    OR o.name       LIKE @pattern ESCAPE N'\')

To see where in a long definition the term appears, return the position and a window of text around it. CHARINDEX returns the 1-based starting position of the first match, or 0 when there is none. SUBSTRING with a start below 1 shortens the returned text, so the CASE keeps the window from starting before the first character:

 1SELECT
 2    o.name                                      AS object_name,
 3    CHARINDEX(@search_string, m.definition)     AS match_position,
 4    SUBSTRING(m.definition,
 5        CASE WHEN CHARINDEX(@search_string, m.definition) > 100
 6             THEN CHARINDEX(@search_string, m.definition) - 100
 7             ELSE 1 END,
 8        300)                                    AS match_context
 9FROM sys.sql_modules AS m
10INNER JOIN sys.objects AS o ON m.object_id = o.object_id
11WHERE CHARINDEX(@search_string, m.definition) > 0;

CHARINDEX compares using the input collation and limits the term it looks for to 8,000 characters, so the same NVARCHAR(256) variable fits. It reports only the first occurrence in each definition, so a module with several hits shows one window.

To include DDL triggers, which sys.objects leaves out, query sys.triggers joined to sys.sql_modules on object_id and filter on parent_class_desc. sys.triggers is the view that holds all triggers, DML and DDL. The same idea extends past the database: a broader search can also cover server triggers, SQL Agent job steps, and linked servers.

Finally, when the search is the first step of a rename, search for the old name once more after the change. Because of the sp_rename behaviour described above, any module whose stored text still shows the old name needs a drop-and-recreate with the new one.

Conclusion

Searching sys.sql_modules through a join to sys.objects is the direct way to ask which procedures, views, functions, and DML triggers mention a given word. Escaping the wildcards keeps the match literal, the type filter narrows the output, and the match-position variant makes long definitions readable. Know its blind spots: encrypted modules return a NULL definition, DDL triggers sit outside sys.objects, and permissions decide which rows you see. Within those limits it is the quickest pre-change impact check available without any extra tooling.

References

Posts in this series