SQL Server Search Stored Procedure and View Text

calendar Apr 9, 2026 / · 6 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

Search Stored Procedure and View Definitions in SQL Server

This script searches the text of stored procedures, views, functions, and triggers in the current database using sys.sql_modules, returning any programmable object whose name or definition contains a given keyword.

Purpose and Overview

Finding which stored procedures, views, or functions reference a specific table, column, or keyword is a routine DBA and developer task. It comes up when renaming a column, tracking down where a piece of business logic lives, auditing who calls a particular table, or investigating the impact of a schema change. SQL Server stores the source definition of every programmable object in sys.sql_modules, making it straightforward to search across all object types in a single query. This script, adapted from the sqlserver-kit community collection by Ed Pollack and Konstantin Taranov, queries sys.sql_modules joined to sys.objects to search both object names and their full definitions, returning results with the object type, schema, and full source text.

Code Breakdown

The complete script is shown below, followed by an explanation of each section.

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

sys.sql_modules

sys.sql_modules holds one row for every SQL object whose definition is stored in the database: stored procedures, views, scalar functions, table-valued functions, inline table-valued functions, DML triggers, and rules. The definition column contains the full CREATE statement text exactly as it was submitted. This is the same text returned by OBJECT_DEFINITION() and by the SSMS "Script as CREATE" option.

Notable characteristics:

  • definition is NVARCHAR(MAX), supporting very long object definitions
  • Encrypted objects (WITH ENCRYPTION) have a NULL definition — they do not appear in search results
  • System objects are included unless filtered out; joining to sys.objects and filtering on o.is_ms_shipped = 0 excludes them if needed

Joining sys.objects

sys.objects provides the object name, schema, and type code. The join on m.object_id = o.object_id links each definition to its object metadata. The CASE expression on o.type translates single-character type codes into readable labels. The ELSE o.type_desc fallback returns the full type description for any type not explicitly listed.

SCHEMA_NAME(o.schema_id) converts the schema ID to its name. This is important in databases with multiple schemas — a stored procedure named GetOrders in dbo is different from one in sales.

Search Parameter

@search_string is declared as NVARCHAR(256) so the LIKE comparison uses Unicode-safe matching. The N'%' + @search_string + N'%' pattern wraps the term in wildcards for a contains search. To search for an exact match, remove the percent signs. To search for a term at the start of a definition, use @search_string + N'%'.

The WHERE clause searches both m.definition (the full source text) and o.name (the object name). This means the query returns objects whose name matches the term even if the definition does not contain it — useful for finding a stored procedure by name when you only remember part of it.

Ordering

ORDER BY o.type, o.name groups results by object type (all procedures together, all views together) and sorts alphabetically within each type. Swap to ORDER BY o.name if you prefer a flat alphabetical list regardless of type.

Key Benefits and Use Cases

  • Finds all programmable objects that reference a table, column, or keyword — essential before renaming or dropping an object
  • Covers stored procedures, views, scalar functions, table-valued functions, and triggers in one query
  • Returns the full definition text so you can read the context without opening each object individually
  • Object type column makes it easy to filter results to just procedures or just views after exporting
  • Works on all SQL Server editions; no special permissions beyond database access required
  • Run without modification in any database — DB_NAME() and SCHEMA_NAME() adapt automatically

Performance Considerations

  • Definition column scanning: sys.sql_modules.definition is NVARCHAR(MAX) with no full-text index in most environments. The LIKE search performs a full column scan. On databases with thousands of large stored procedures this can take several seconds. It is not suitable as a real-time application query.
  • Encrypted objects: Objects created WITH ENCRYPTION have a NULL definition and will not match any search. If you need to search encrypted objects, you must first decrypt them or review them in SSMS with the appropriate decryption key.
  • Cross-database search: sys.sql_modules is database-scoped. To search across all databases on an instance, wrap the query in a cursor or use sp_MSforeachdb:
    1EXEC sp_MSforeachdb
2    'USE [?]; SELECT DB_NAME(), o.name, o.type_desc, m.definition
3     FROM sys.sql_modules m JOIN sys.objects o ON m.object_id = o.object_id
4     WHERE m.definition LIKE ''%YourTerm%''';
  • OBJECT_DEFINITION alternative: For a quick single-object check, SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.ProcedureName')) is faster than querying sys.sql_modules directly. Use the full query when you need to search across all objects.

Practical Tips

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

1WHERE (m.definition LIKE N'%' + @search_string + N'%'
2    OR o.name LIKE N'%' + @search_string + N'%')
3    AND o.type = 'P'

To exclude Microsoft-shipped system objects from results:

1INNER JOIN sys.objects AS o
2    ON m.object_id = o.object_id
3WHERE o.is_ms_shipped = 0
4    AND (m.definition LIKE N'%' + @search_string + N'%'
5         OR o.name LIKE N'%' + @search_string + N'%')

To also return the line number within the definition where the match appears — useful for large procedures — split the definition on newlines using STRING_SPLIT (SQL Server 2016+) or CHARINDEX to locate the offset:

1SELECT
2    o.name                                          AS object_name,
3    CHARINDEX(@search_string, m.definition)         AS match_position,
4    SUBSTRING(m.definition,
5        CHARINDEX(@search_string, m.definition) - 100,
6        300)                                        AS match_context
7FROM sys.sql_modules AS m
8INNER JOIN sys.objects AS o ON m.object_id = o.object_id
9WHERE m.definition LIKE N'%' + @search_string + N'%';

The match_context column returns 300 characters centered around the match, giving enough surrounding text to understand the usage without reading the full definition.

Conclusion

Searching object definitions through sys.sql_modules is the standard SQL Server approach for finding which stored procedures, views, and functions reference a given keyword. Joined to sys.objects, a single query covers all programmable object types in the current database, returning the schema, name, type, and full definition of every match. For cross-database searches, wrap it in sp_MSforeachdb. For targeted use, filter by type or extend with CHARINDEX to locate the match position within large definitions. This is one of the most frequently used scripts in a DBA's daily toolkit.

References

Posts in this series