Search Stored Procedures

Date Published: 02 July 2008

Search Stored Procedures

Sometimes, especially on very old applications that have gone through several rewrites but are still using the original database, I find myself wondering which stored procedures reference a given table, or each other, or whether changing the name of a view or column name will break something somewhere in the database. There are some tools out there to help this kind of thing, such as Red Gateโ€™s Refactor tool, but at a simpler level if you just need to search your stored procedures for a particular string, you can do it using this query that I just saw come across the Sql Server SQL list on SQL Advice:

<span style="color: #0000ff">SELECT</span> ROUTINE_NAME, ROUTINE_DEFINITION 
<span style="color: #0000ff">FROM</span> INFORMATION_SCHEMA.ROUTINES 
<span style="color: #0000ff">WHERE</span> ROUTINE_DEFINITION <span style="color: #0000ff">LIKE</span> <span style="color: #006080">'%lq_Campaign%'</span> 
<span style="color: #0000ff">AND</span> ROUTINE_TYPE=<span style="color: #006080">'PROCEDURE'</span>

Iโ€™m not sure where Keith found it, but it works great for my needs, and hopefully youโ€™ll find it useful as well.

Steve Smith

About Ardalis

Software Architect

Steve is an experienced software architect and trainer, focusing on code quality and Domain-Driven Design with .NET.