Find all stored procedures that reference a specific column in some table

Practical: Ways to find a needle in a haystack

Favoured query with sys.objects

Adopting sys.objects in your journey provides a direct and focused approach for uncovering specifics:

SELECT DISTINCT p.name
FROM sys.objects p
WHERE OBJECT_DEFINITION(object_id) LIKE '%YourColumnName%'
AND type_desc = 'SQL_STORED_PROCEDURE' -- because tables cannot talk


Preferable for sizable databases due to its performance advantage by limiting the search to stored procedures.

Nifty duplicate removal with DISTINCT

Dealing with duplicate entries? Use DISTINCT to filter them out by avoiding listing the same procedure name more than once.

Global search with information_schema

For a more wholesome coverage, checking views and triggers included, Let's extend the search scope to information_schema:

SELECT routine_name
FROM information_schema.routines
WHERE routine_definition LIKE '%YourColumnName%'
AND routine_type='PROCEDURE'


Be aware that information_schema may not show complete definitions for long procedures.

Hello Graphics, Meet ApexSQL Search

For fans of visuals, ApexSQL Search is a handy utility that blends seamlessly with SSMS to expose object relationships graphically.

Paddle Deep: Advanced considerations

Pattern matching caveats

While using LIKE '%' + YourColumnName + '%', consider the idiosyncrasies of pattern matching. Be as precise as possible in your search by including table names, or alias names to prevent any column-name mix-ups.

Integrated Solutions: C# meets SQL

To incorporate SQL logic into a C# console project for those managing hybrid environments:

// Awaiting your magic ADO.NET spell to execute SQL commands and retrieve the results


SQL searches extending beyond SSMS horizon!

More power to you with Red Gate SQL Search

Explore third-party solutions like Red Gate SQL Search for a faster and broader search span across many databases and servers - because who doesn't love a powerful spell?

Ensuring search accuracy

For increased specificity, include the table name in your search:

WHERE m.definition LIKE '%YourTable.YourColumnName%'
OR m.definition LIKE '%YourTableAlias.YourColumnName%'


You don’t want a different column from another table crashing your party!

Pro-Level: Maximum efficiency and minimum ambiguity

Regular Audits: The proactive approach

You can schedule automatic routine audits of your database objects with SQL Agent Jobs. Why wait for the trouble to knock?

Dynamic SQL: The masked villain

For dynamic SQL within procedures, traditional methods may fail to identify these references. To catch these, consider using full-text search or examining the sql_handle of sys.dm_exec_query_stats DMV. Surprise!

Renamed columns: The identity crisis

For recently renamed columns, also check version control systems to see if old procedure code needs an update. It's like refreshing their memory!

Optimize your query: The art of performance

Design your queries considering database size and effective indexing strategies. And, when you can, schedule your searches during low-activity periods to maintain your performance.