[Dan Cantley]

We have a SQL script that we run when moving AX PROD to a DEV (or any of our other) system.  It works fine, but we have some views used by our EDI system that aren’t part of the script that I need to do a manual change on after we finish up with all the other activities.  I looked in a few different places in Google Academy for scripts that could do this for me but only found bits and pieces but nothing that says ‘this is the way to do it!’.  After doing my research I finally threw this script together and it looks like it works.  I put the declarations for what gets change near the start – so it is easy to change for different restorations.  Not sure I like the Recursive REPLACE.  I would need to add to it if there are other changes needed – could get quite long.  For this one – would only need to change the italic fields – replacing the PRODUCTION, DEVELOPMENT and ViewName with whatever text to make it work.  The CREATE VIEW and ALTER VIEW wouldn’t change in the script.  But it would change CREATE to ALTER to update the VIEW in whatever system this is run.  We have some spots in the View that has the Database name hard-coded.

Does anybody see any holes in this setup?  Is this a workable way of automating the View Changes when refreshing out systems from PROD?

We have 3 Views that I would need to run this for.  I am thinking there would be an issue with using DECLARE if I need to change it in the same group of scripts (I haven’t tested that aspect of it yet)  If so, I can take the DECLARE statements back out and use the strings instead.

IF OBJECT_ID(‘tempdb..#Tmp01’) IS NOT NULL
DROP TABLE #Tmp01;

DECLARE @FromDatabase nvarchar(20) = ‘PRODUCTION‘
DECLARE @ToDatabase nvarchar(20) = ‘DEVELOPMENT‘
DECLARE @Create nvarchar(20) = ‘CREATE VIEW’
DECLARE @Alter nvarchar(20) = ‘ALTER VIEW’
DECLARE @View nvarchar(20) = ‘ViewName‘

SELECT sm.definition INTO #TMP01
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE type = ‘V’ AND OBJECT_Name(sm.object_Id) = @View

update #tmp01
set definition = REPLACE(REPLACE(definition, @FromDataBase, @ToDatabase), @Create, @Alter)

Select * from #tmp01

DECLARE @script NVARCHAR(MAX) = (select definition From #tmp01)

BEGIN EXEC(@script) END
DROP TABLE #Tmp01;
?

I tried using: SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME= @View for the first select, but the VIEW_DEFINITION size was too small to pull in the whole script that was stored in the database, so I was stuck with the SELECT with a JOIN.  

I know there are usually multiple ways to do the same task – but this is the best I came up with for now.

——————————
Dan Cantley
Systems Analyst
Bedrock Manufacturing Co, LLC
Detroit MI
——————————

[Alex Meyer]

Dan,

Your solution would absolutely work, but there is quite a bit of overhead for maintaining it.

There are a couple different ideas I would try to help with this:

1) Do you absolutely need the database names hard corded in the scripts? I know this may seem trivial but if all of your scripts are going off the same database then the database name is not technically needed.
2) Use one of the techniques in the following links:
Find and replace database name in views and stored procedures

Using hardcoded database names in query when database names are different on development and production server – SQLServerCentral

Global variables in SQL Server

——————————
Alex Meyer
Director of Dynamics AX/365 for Finance & Operations Development
Fastpath
Des Moines, IA
——————————
——————————————-