Tuesday, 7 January 2014

Script to replace a string in all programming scripts




After Database migration, sometimes programmers\DBA needs to change a specific string (ex. DB Name\owner Name etc...) in all programming scripts.

Below script is used to change the database name from Old_DB to New_DB in all procedures, Triggers and Views.

Script:

SELECT
            'GO
            ' +  REPLACE(REPLACE(REPLACE(REPLACE([definition], '[Old_DB]', '[New_DB]'), 
            'CREATE PROCEDURE', 'ALTER PROCEDURE'),
            'CREATE TRIGGER', 'ALTER TRIGGER'),
            'CREATE VIEW','ALTER VIEW')
FROM
            sys.sql_modules
WHERE
            [definition] LIKE '%[Old_DB]%';