Creating the same view in multiple tenant databases using a stored procedure

Question

I try to create/alter the same view in a list of databases. Let's call them A, B, C.

The best would be to create/alter them all at once so I'm looking into a stored procedure to create the view:

DECLARE @DatabaseName NVARCHAR(128)

DECLARE db_cursor CURSOR FOR
SELECT name 
FROM sys.databases 
WHERE name IN ('A', 'B', 'C')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Construct the dynamic SQL with the USE statement included
    DECLARE @SQL NVARCHAR(MAX) = '

    USE ' + @DatabaseName + ';
    CREATE OR ALTER VIEW [dbo].[View] AS
    SELECT * FROM magic_table'

    EXEC sp_executesql @SQL;

    FETCH NEXT FROM db_cursor INTO @DatabaseName
END

CLOSE db_cursor
DEALLOCATE db_cursor

When I try this the I get the following error

Msg 111, Level 15, State 1, Line 5
'CREATE VIEW' must be the first statement in a query batch.

For my idea the statement is the first in a query batch. Separating the USE and the CREATE/ALTER VIEW does not work because the database context is lost for the CREATE/ALTER.

What would be the best way to proceed? Or how to make sure the CREATE OR ALTER VIEW command has a database in which the view has or will be created? Alas, A.dbo.View does not work

Answer 1

Rather that injecting the name of the database into the dynamic statement, you can actually make the entire thing non-dynamic and parametrise the procedure you want to execute. Procedure are the only object where EXEC @VariableName is valid syntax, and will actually execute the object with the name stored in the variable. As such, instead, you can assign the name of the procedure to execute, in this case <Database Name>.sys.sp_executesql. This gives the following:

DECLARE @DatabaseName nvarchar(128);

DECLARE db_cursor CURSOR FAST_FORWARD FOR
    SELECT name
    FROM sys.databases
    WHERE name IN ('A', 'B', 'C');

OPEN db_cursor;
FETCH NEXT FROM db_cursor
INTO @DatabaseName;

DECLARE @Procedure nvarchar(500);

WHILE @@FETCH_STATUS = 0 BEGIN
    -- Construct the dynamic SQL with the USE statement included
    SET @Procedure = QUOTENAME(@DatabaseName) + N'.sys.sp_executesql';

    EXEC @Procedure N'CREATE OR ALTER VIEW [dbo].[View] AS SELECT /* Replace me with a distinct list of columns, do not use *.*/ FROM dbo.magic_table;';

    FETCH NEXT FROM db_cursor
    INTO @DatabaseName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;