How to do bulk update of views?

Question

My database has about 30 views, most of which have a reference to another database on this server (call it DB1).

Now, without going into the reasons why, I need to update all those views to DB2, also on the local server.

I would hate to have to do this manually on each view. Is there some SQL query I can run that will replace all occurrences of the string 'DB1' with 'DB2' in all my views?

Answer 1

navigate to the views folder, press F7 (Object Explorer Details) now from the right pane select all the views you want, right click-->script view as--> DROP and CREATE TO --> new query window. In there change DB1 to DB2 and voila

see image

Answer 2

You could query sys.sql_modules like this, which I'd use to generate a file via bcp (SSMS will truncate results) which can be run as another SQL script. This preserves permissions too.

I've used this technique before and it works.

SELECT
    REPLACE (REPLACE (sm.definition, 'CREATE VIEW', 'ALTER VIEW'), 'DB1.', 'DB2.') + '
    GO'
FROM
    sys.sql_modules sm
    JOIN
    sys.objects o ON sm.object_id = o.object_id
WHERE
    sm.definition LIKE '%DB1.%' AND o.type = 'V'