Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 we create in this database.

The problem as I understand it stems from three rules in SQL regarding the EXEC command

  1. USE contexts are only alive for the life of the EXEC
  2. 'CREATE VIEW' must be the first statement in a query batch
  3. GO isn't actually a SQL Command and as such isn't allowed in dynamic sql

So here are three things I've tried without success.

--1.Results in my view not being created in my database
EXEC ('USE [' + @databaseName + ']')
EXEC ('CREATE VIEW')

--2.Results in a 'CREATE VIEW' must be the first statement in a query batch
EXEC 
('
    USE [' + @databaseName + ']
    CREATE VIEW
')

--3.Results in Incorrect syntax near 'GO'
EXEC 
('
    USE [' + @databaseName + ']
    GO
    CREATE VIEW
')

Any suggestions? I think this should be a common use case but Google wasn't able to help me.

share|improve this question
1  
Couldn't you just say CREATE VIEW {database}.{schema}.{viewname} ... ? –  Ken Richards Jan 16 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 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 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 at 21:56
add comment

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
Awesome thank you. Here is another good answer stackoverflow.com/a/21173560/1066291 –  Mark Rucker Jan 16 at 22:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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