On occasion I have found it useful to search for stored procedures based on containing text.
It doesn’t happen often as your naming convention should be enough to explain the purpose of your procedure, and be easy to find. However, in the real world, when looking at legacy systems it may be of use.
Another step before searching by text could be to right-click on a table or object in SQL Server object explorer and select “View dependencies” this will list all associated objects that the table relies on, or in turn which rely on the table.
But, finally if those steps have failed there is a way to search by text. Though inefficient, it can be a useful too for a SQL admin.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%SearchString%'
AND ROUTINE_TYPE='PROCEDURE'
Phil's learning log 