SQL Script to Alter Server, Instance and Database References in Views After Copying Microsoft Dynamics GP Databases To A New Server

Four years ago I wrote a script which changed server and database names embedded within SQL views after a live Microsoft Dynamics GP company had been copied into a test one. This script is used by a client as one of several which are all run when they refresh live into test, but they also sometimes use it when copying databases between a live server and a test one.

They recently ran it when creating a new test server, but found afterwards that it hadn’t worked correctly. Or more accurately, it had worked correctly as it was created to do. However, the new SQL Server had a different Instance name than the original.

The script below is an update of the original which adds in a change of Instance alongside the existing server and company.

The three highlighted sections are for the old values which need to be changed; the new values are all determined automatically based on the database in which the script is run.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @ViewDefinition AS NVARCHAR(max)
DECLARE @OldServer AS VARCHAR(50) = '2018SQL1'

DECLARE @NewServer AS VARCHAR(50) = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(50))
DECLARE @OldDB AS VARCHAR(50) = 'FINANCE'

DECLARE @NewDB AS VARCHAR(50) = CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(50))
DECLARE @OldCompany AS VARCHAR(5) = 'TWO'

DECLARE @NewCompany AS VARCHAR(5) = DB_NAME()
CREATE TABLE #ViewDefinitions(

	ViewDefinition NVARCHAR(MAX)

	,ROW_ID INT IDENTITY

)
INSERT INTO #ViewDefinitions

	(ViewDefinition)

--VALUES

	(SELECT

		REPLACE(

			REPLACE(['SQL Modules'].definition, 'CREATE VIEW', 'ALTER VIEW')

		,'Db=' + @OldDB + 
'&Srv=' + @OldServer + '&Cmp=' + @OldCompany,'Db=' + @NewDB + '&Srv=' + @NewServer + '&Cmp=' + @NewCompany)

	FROM

		sys.all_views AS ['All Views']
	JOIN

		sys.sql_modules AS ['SQL Modules']
			ON

				['SQL Modules'].object_id = ['All Views'].object_id

	and

		['SQL Modules'].definition LIKE '%Db=' + @OldDB + '&Srv=' + @OldServer + '&Cmp=' + @OldCompany + '%')
DECLARE

	cursor_Views Cursor

FOR

	SELECT

		ViewDefinition

	FROM

		#ViewDefinitions
	Open cursor_Views
	FETCH NEXT FROM

		cursor_Views

	INTO

		@ViewDefinition

	WHILE (@@FETCH_STATUS <> -1)

		BEGIN

		IF (@@FETCH_STATUS <> -2)

			EXEC (@ViewDefinition)

			FETCH NEXT FROM

				cursor_Views

			INTO

				@ViewDefinition

		END

	CLOSE cursor_Views

DEALLOCATE cursor_Views
DROP TABLE #ViewDefinitions

GO

As always before using a script, make sure you understand wat it is going to do and also ensure you have a good backup.

Read original post SQL Script to Alter Server, Instance and Database References in Views After Copying Microsoft Dynamics GP Databases To A New Server at azurecurve|Ramblings of an IT Professional