Global Find and Replace - Multiple procedures and functions

[BG12424]

Is it possible to loop through all stored procedures and functions to replace "_bak" with an empty space. I tried using the INFORMATION_SCHEMA.ROUTINES table, but SQL Server did not let me make the update. Is there a safe and automated way to do this? Thanks regards,
Brian

 

[osjohnm]

Hi Brian

The one way that I can think of is to change the text field in the syscomments table. But to do this you will have to allow updates directly to the system tables.

You could join the sysobjects an syscomments table like so:

select so.name, sc.text
from syscomments sc, sysobjects so
where so.xtype = 'P'
and so.id = sc.id

You would need a cursor to loop thru all of the procedures and then run a replace(text, '_bak', '') statement on the text column as you loop thru each record. Updating the system tables is not always a good idea.

Or alternatively right click on the database> all tasks> generate sql scrip> choose all stored procs and functions> create and drop option> script object-level permissions.

Once you have the script in query analyser use the find/replace under the edit menu and then replace all the "_bak" with a space. Then execute the script after hours and after a backup and then it will drop and re-create all the procs and functions without the "_bak" and setup all the permissions again.

Either way make sure you backup the database and do this after production hours incase something goes wrong.

Hope this helps

John

 

[osjohnm]

Brian

Sorry but the first part of my post won't work as I was just reminded that the text column in syscomments is a computed column and can't be edited. So the second option is probably the best idea.

John

 

[BG12424]

Thanks for the help. I guess it's for the best anyhow to just script the procedures and do a global find/replace in query analyzer. regards,
Brian