I ran across a problem the other day, needing to change in bulk a lot of stored procedures on my cloud server. The requirement was to change a join condition from a text field to an integer – which is in itself a better idea as numeric indexes perform better.
First, I wrote a query to allow me to find the stored procedures containing the text I wanted to modify:
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%Foo%' AND ROUTINE_TYPE='PROCEDURE'
SELECT OBJECT_NAME(id) FROM SYSCOMMENTS WHERE [text] LIKE '%Foo%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id)
SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND definition LIKE '%Foo%'
Second, I get the text of the stored procedure I wish to modify, and I run sp_helptext to get the details:
I go through all the procedures like this, modifying when needed, re-running the original statement, until there are no other results left.