A setting name appears somewhere in the database, but nobody remembers where. Searching every stored procedure starts with sys.sql_modules and ends with checking what each match really means.
Start Searching Every Stored Procedure With sys.sql_modules
sys.sql_modules holds T-SQL definitions for procedures, views, functions, and triggers that expose their text. Join it to sys.objects and sys.schemas so each result has a useful name. Filter to procedures if that is the request. A broad module search can be useful when the word lives in a view.
I search the smallest scope first. A result set with every occurrence of a common word is harder to review than a targeted search in one database. Use a stable schema and object name in the output so another DBA can open the definition quickly.
What does a hit prove? Only that the text appears in the module definition. It can be in executable SQL, a comment, or a string literal. Read the surrounding code before making a change.
DECLARE @term nvarchar(100) = N'OldColumnName';
SELECT SCHEMA_NAME(o.schema_id) AS schema_name,
o.name AS procedure_name
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id = m.object_id
WHERE o.type = 'P'
AND m.definition LIKE N'%' + @term + N'%'
ORDER BY schema_name, procedure_name;Understand Case and Collation
LIKE follows the collation of the expression. In a case-insensitive database, OldName and oldname can match the same search. In a case-sensitive database, they can differ. If case matters for the investigation, apply a chosen collation to the definition in the query.
A forced collation can make the scan more work, but module text searches are investigative rather than high frequency report queries. Document which comparison you used. A result set from one database can differ from another because their collations differ.
I test the search term with a known procedure before trusting a zero result. An empty result can mean no match, missing metadata visibility, encrypted definition, wrong database, or different spelling. The search query should not be treated as an oracle.
SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
OBJECT_NAME(object_id) AS object_name
FROM sys.sql_modules
WHERE definition COLLATE Latin1_General_100_CS_AS
LIKE N'%OldColumnName%';Separate Comments From Live References
A procedure definition can mention a table in a comment or an error message without querying it. A text search is a candidate list. Open the matching module and check the actual statement and branch. A commented old name can be harmless while a dynamic SQL string can be critical.
Dependency views offer a second angle for references that SQL Server can bind, but they miss some dynamic SQL and cross-database details. Use both when a schema change is planned. Search application source and jobs too. Database modules are only one part of the consumer map.
I keep a review column beside each hit: active reference, comment, test code, or unrelated word. That small classification makes the result useful to a migration plan. A raw list of names can look complete while hiding the hard cases.
Extend Searching Every Stored Procedure to All Databases
sys.sql_modules is scoped to the current database. For an instance-wide search, enumerate accessible user databases and run the query in each. Use QUOTENAME for database identifiers if building dynamic SQL. Do not concatenate untrusted text into the SQL statement. Pass the search term as a parameter.
Check database state and access before opening each one. An offline database or denied metadata permission should appear in the report as unsearched, not as no matches. Record the database name with every result. Cross-database references can be found in a module stored elsewhere.
I prefer a controlled script that logs failures per database over one large dynamic batch that stops at the first error. The task is discovery. A partial search should be labeled partial. A green grid with missing databases is worse than an honest exception.
SELECT name, state_desc, HAS_DBACCESS(name) AS has_access
FROM sys.databases
WHERE database_id > 4
ORDER BY name;Know What Searching Every Stored Procedure Cannot Show
Encrypted modules do not expose their definitions through sys.sql_modules. Permissions can also limit metadata visibility. Dynamic SQL assembled at run time can contain names in fragments that a simple LIKE will miss. SQL Agent steps, SSIS packages, application code, and report datasets live outside this view.
A synonym can hide the base object name from a text search. A procedure can call another procedure whose definition holds the actual reference. Trace the call chain when the change is important. Do not stop after one procedure name appears.
I ask the owner for source definitions and deployment records when catalog visibility is incomplete. That is more reliable than guessing. The database can tell you what it stores and exposes, not everything the wider application knows.
Handle Special Search Characters
LIKE treats percent, underscore, and brackets as pattern syntax. A literal search term containing those characters needs escaping. For a simple word without them, the query is straightforward. For identifiers with underscores, a naive pattern can match more than expected.
Use a consistent escaping routine or search the returned definitions in a tool that supports literal text matching. Do not confuse extra matches with a SQL Server defect. The query asked for a pattern. It received one.
I test a distinctive full identifier when possible, then widen the search. Searching only a short fragment can flood the result set. A staged search helps: exact known name, likely abbreviation, then a broader word. Keep the search terms in the investigation notes.
SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
OBJECT_NAME(object_id) AS object_name
FROM sys.sql_modules
WHERE definition LIKE N'%Order[_]Id%';Turn Search Results Into a Change List
For every active match, note the procedure, database, owner, execution path, and required edit. Recompile or execute representative calls after the change. A search result alone does not prove a branch works. Include rarely run jobs in the test plan.
Rerun the same text search after deployment and review remaining hits. Some can be intentional compatibility code or comments. Record why they remain. If the target name appears in external application SQL, get that consumer into the same plan.
Searching every stored procedure is a practical first step, not a complete dependency guarantee. Use catalog text, dependency metadata, and source ownership together. That gives you a list a person can act on rather than a reassuring count.
A text match identifies a candidate, not a dependency. Dynamic SQL can assemble an object name at runtime, and comments can contain a word that never executes. After finding a match, inspect the module text and trace how the statement is reached. Search deployment scripts as well as the database when a renamed column is involved. Which results are active references, and which are just examples left in comments?
Related reading on this blog: Easiest Way to Copy All Stored Procedure Definitions and Case-Sensitive Search.
A module text match is not a dependency verdict, it is a lead that needs review.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
