Friday, September 14, 2007

How to search all stored procedures in a db for a particular "text"

After migrating a large db from SQL Server 2000 to SQL Server 2005, one of the changes I needed to make was to switch from using xp_smtp_sendmail to sp_send_dbmail using the new "Database Mail" feature. Since I'm dealing with a db with at least a couple hundred stored procedures, I didn't want to manually look at each one. Here is a blogpost I found tackling this issue.

Summarizing, here's what I have used:

use mydb;

select routine_name, routine_definition
from information_schema.routines
where routine_definition like '%xp_smtp_sendmail%'
and routine_type = 'procedure'