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%'