“Find all the stored procedures that contain cursors”
“Do any of the stored procedures have temporary tables”
“Have you used function xyz in any of the procedures”
This is a difficult; I have a site that has over 600 stored procedures, and I recently needed to change a type of a major field in a table. I need to find all the stored procedures that access this field and make sure that the type is correct.
In SQL Server, use the following command filling in MY_SEARCH appropriately. This will return to you all the stored procedures that contain your search terms.
For shorter than 4000 character stored procedures, this is fine
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%MY_SEARCH%' AND ROUTINE_TYPE='PROCEDURE'
This route is better and removes the 4000 character limitation
SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id) FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE '%MY_SEARCH%'