Thursday, 21 November 2013

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]%';