Script to replace a String in entire programming script in SQL Server

During server\database migration, we may need to replace a specific string in all the programming script like stored procedures, Triggers and Views. It is really very difficult to go and replace in each procedure separately. Below script will produce the source code by replacing the specific string in all the programming script. We can simply copy and execute it in query analyzer. This makes life easier.

The following script will replace “TestSystems” by “PRODSystems”.

Script

SELECT 'GO

            ' +  REPLACE(REPLACE(REPLACE(REPLACE([definition], '[TestSystems]', '[PRODSystems]'), 

            'CREATE PROCEDURE', 'ALTER PROCEDURE'),

            'CREATE TRIGGER', 'ALTER TRIGGER'),

            'CREATE VIEW','ALTER VIEW')

 FROM

                        SYS.SQL_MODULES

 WHERE

                        [definition] LIKE '%[TestSystems]%';