Sql-server – Update audit database name in triggers and views of main database

sql serversql-server-2008trigger

I have a a system with two databases, main database and audit database. A lot of the triggers and table views in the main database and audit database are referencing from one database to the other. No I needed to change both databases names but unfortunately they failed to work because they still have the old names in the code.

Is there a code to search and replace the old name used for referencing or in dependence?

Thank you,

Best Answer

You can use the text column from sys.all_sql_modules to create alter scripts with your new database names. Below is an example where I use AdventureWorks2014 to replace any text where there is a CREATE with an ALTER while changing database name HumanResources to HR. Again this will only generate your scripts.

EDIT: Replaced syscomments with all_sql_modules. Thanks Kenneth Fisher!

USE AdventureWorks2014
GO

SELECT 
    o.name,
    o.type,
    o.type_desc,
    sc.definition,
    REPLACE(REPLACE(sc.definition, 'CREATE ', 'ALTER '),'[HumanResources]','[HR]')
FROM sys.all_sql_modules sc
    inner join sys.objects o 
        on sc.object_id = o.object_id
WHERE o.type IN ('V','TF','P','TR') --Views, Table Function, Proc, Trigger. You can include/exclude additional types if you choose.

Hope this helps!

Related Question