How to Search All Stored Procedures for Text

I needed to look through my hundreds of stored procedures to find which of them contained a reference to a specific table. In the past I’ve used desktop applications like Toad from Quest to do this, but I didn’t have an install handy so I tried to find something lighter. You can run this query to show all procedures that contain the word “TESTSEARCH”. You can comment out the routine type in the where clause to also return functions with your search string.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%TESTSEARCH%' 
    AND ROUTINE_TYPE='PROCEDURE'