Find Text in Stored Procedure

Today I was trying to debug an error that was occurring in an old .NET 1.1 and SQL Server 2000 application. I needed to find out how a particular value was being set in a DataSet. I knew the value was being returned via a stored procedure, and due to naming conventions this stored procedure starts with ‘Select’, so I wrote a script to find all stored procedures containing the appropriate text.

To do this I used the Routines view as this contains a row for each function and stored procedure in the database.

SELECT 
	Routine_Name
,	Routine_Definition     
,	Last_Altered
FROM 
	Information_Schema.Routines
WHERE 
	Routine_Definition LIKE '%Text To Find%' 
AND	Routine_Type = 'PROCEDURE' -- or 'FUNCTION'
AND	Routine_Name LIKE 'Select%'