Deploy SQL View to All Databases

I have a few clients who have quite a few company databases in Microsoft Dynamics GP. One of them has well over a hundred live companies. This can make deploying reports somewhat long winded when you need to deploy an SQL view to all of the databases.

Fortunately, Microsoft SQL Server has ways and means which you can use to make the process a lot easier. In this case, I am using a SQL cursor to select all of the databases from the Company Master (SY01500) and loop through them to deploy the view; the deployment is in three phases:

• Delete any existing view with the same name (this allows for an easy redeployment).
• Create the view.
• Grant the SELECT permission to DYNGRP.

The script is posted below with a simplified PO report being created; the view name is set in the highlighted parameter near the top of the script.

The large highlighted section is where you please the content of the view which is to be deployed.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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 @IntercompanyID AS VARCHAR(5)
DECLARE @SQLStatement AS VARCHAR(8000)
DECLARE @SQLViewName AS VARCHAR(100) = 'uv_AZRCRV_POReport'

DECLARE
	cursor_InterID Cursor
FOR 
	SELECT
		INTERID
	FROM
		SY01500
	INNER JOIN
		master..sysdatabases
	ON
		name = INTERID
	
	OPEN cursor_InterID

	FETCH NEXT FROM
		cursor_InterID
	INTO
		@IntercompanyID
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)
			-- drop view if it exists
			SET @SQLStatement = 'EXEC ' + @IntercompanyID + '..sp_executesql N''IF OBJECT_ID (N''''' + @SQLViewName + ''''', N''''V'''') IS NOT NULL DROP VIEW ' + @SQLViewName + ''''
			EXEC (@SQLStatement)
			-- create view
			SET @SQLStatement = 'EXEC ' + @IntercompanyID + '..sp_executesql N''CREATE VIEW ' + @SQLViewName + ' AS
			SELECT
				DB_NAME() AS DataBaseName
				,SY.CMPNYNAM
				,PONUMBER
				,CASE WHEN POSTATUS = 1 THEN
					''''New''''
				WHEN POSTATUS = 2 THEN
					''''Released''''
				WHEN POSTATUS = 3 THEN
					''''Change Order''''
				WHEN POSTATUS = 4 THEN
					''''Received''''
				WHEN POSTATUS = 5 THEN
					''''Closed''''
				WHEN POSTATUS = 6 THEN
					''''Cancelled''''
				ELSE
					''''Unknown''''
				END AS POSTATUS
				,CASE WHEN POTYPE = 1 THEN
					''''Standard''''
				WHEN POTYPE = 2 THEN
					''''Drop Ship''''
				WHEN POTYPE = 3 THEN
					''''Blanket''''
				WHEN POTYPE = 4 THEN
					''''Blanket Drop Ship''''
				ELSE
					''''Unknown''''
				END AS POTYPE
				,DOCDATE
				,SUBTOTAL
				,REMSUBTO
				,VENDORID
				,VENDNAME
			FROM
				POP10100 WITH (NOLOCK)
			INNER JOIN
				' + DB_NAME() + '..SY01500 AS SY WITH (NOLOCK)
					ON
						INTERID = DB_NAME()'''
			EXEC (@SQLStatement)
			-- grant select permissions to DYNGRP
			SET @SQLStatement = 'EXEC ' + @IntercompanyID + '..sp_executesql N''GRANT SELECT ON ' + @SQLViewName + ' TO DYNGRP'''
			EXEC (@SQLStatement)
			
			FETCH NEXT FROM
				cursor_InterID
			INTO
				@IntercompanyID
		END
	CLOSE cursor_InterID
DEALLOCATE cursor_InterID
GO

Read original post Deploy SQL View to All Databases at azurecurve|Ramblings of a Dynamics GP Consultant