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.