3

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 @databaseName.

I've tried four different ways to create this view without success:

  1. 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 VIEW must 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
    ')
    
  2. To get around (1) I tried to set the context separately so that CREATE VIEW would 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 USE in EXEC only persist until the end of that EXEC statement (details).

    --Result: The view is created in the currently active database rather than @databaseName
    EXEC ('USE [' + @databaseName + ']')
    EXEC ('CREATE VIEW')
    
  3. Next I tried putting everything back into one script but included a GO command in order to make CREATE VIEW the first command in a new query block. This failed because GO isn't allowed within an EXEC script (details).

    --Result: Error message, "Incorrect syntax near 'GO'"
    EXEC 
    ('
        USE [' + @databaseName + ']
        GO
        CREATE VIEW
    ')
    
  4. Finally I tried to specify the target database as part of the CREATE VIEW command. In this case the script failed because CREATE VIEW doesn'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.

  • 1
    Couldn't you just say CREATE VIEW {database}.{schema}.{viewname} ... ? – Ken Richards Jan 16 '14 at 21:50
  • Can you do that? I thought when CREATING VIEWS you can only qualify down to the schema (from MSDN: cl.ly/image/2j001P3m3F3V). I guess I could always try it instead of just assuming I fully understand the documentation. – Mark Rucker Jan 16 '14 at 21:51
  • 1
    Another option is to generate a .sql file and use sqlcmd to execute it. More complicated in some ways and less in others for what you are doing. Primarily though it acts just like a SQL file you are going to run yourself. Among other things you can use GO. – Kenneth Fisher Jan 16 '14 at 21:53
  • Good idea but at that point I think we might as well do a find and replace all on [DatabaseName] and not use dynamic sql at all. That's what we originally had but sometimes not everything was updated and resulted in problems so I wanted a one stop update. We really wanted to make this a one step process instead of a 6 or so step process. – Mark Rucker Jan 16 '14 at 21:56
4

This worked for me Mark

EXEC [OtherDatabase].[sys].[sp_ExecuteSQL] N'CREATE VIEW [SM].[vMyView] AS SELECT ...'

I ran this from the context of the master database and successfully created a new view in the OtherDatabase.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.