I'm trying to create a dynamic database creation script.
There are a lot of steps and we create this database often so the script looks something like this.
DECLARE @databaseName nvarchar(100) = 'DatabaseName' EXEC('/*A lot of database creation code built off of @databaseName*/')
This is all well and good except for one view that we'd like to create in
I've tried four different ways to create this view without success:
My first thought was to simply set the database context and then create the view in one script. Unfortunately, this didn't work because
CREATE VIEWmust be the first statement in its query block (details).
--Result: Error message, "'CREATE VIEW' must be the first statement in a query batch" EXEC (' USE [' + @databaseName + '] CREATE VIEW ')
To get around (1) I tried to set the context separately so that
CREATE VIEWwould be the first command in the
EXEC. This did create the view but did so within my current context and not
@databaseName. It seem that the effects of calling
EXEConly persist until the end of that
--Result: The view is created in the currently active database rather than @databaseName EXEC ('USE [' + @databaseName + ']') EXEC ('CREATE VIEW')
Next I tried putting everything back into one script but included a
GOcommand in order to make
CREATE VIEWthe first command in a new query block. This failed because
GOisn't allowed within an
--Result: Error message, "Incorrect syntax near 'GO'" EXEC (' USE [' + @databaseName + '] GO CREATE VIEW ')
Finally I tried to specify the target database as part of the
CREATE VIEWcommand. In this case the script failed because
CREATE VIEWdoesn't allow the database to be specified as part of its creation (details).
--Result: Error message, "'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name" EXEC ('CREATE VIEW [' + @databaseName + '].[dbo].[ViewName]')
Any suggestions? I think this should be a common use case but Google wasn't able to help me.