USE database_name validation

Trying to c开发者_运维问答reate a script that handles creation of database and schema for various scenarios for automated build scripts. One issue I am running into is USE statement that evaluates database name (verification db exists).

Questions - is there a way to workaround it? Possibly a setting to disable this kind of evaluation my db engine?

Thank you.

Why not check to see if the database already exists?

IF EXISTS(SELECT name from sys.databases where name='DatabaseName')
  SELECT 1
ELSE
  SELECT 0

Put the USE statement in its own batch - it won't be parsed until the database exists.

IF NOT EXISTS(SELECT * from sys.databases where name='dbname')
begin
    create database dbname --any other options needed here
end
GO
use dbname
GO
--now you can carry on in the dbname database

The database name is checked before the script is run, which is easily shown with this little and naive script:

SELECT 'test?'
IF DB_ID('new_db') IS NULL BEGIN
  CREATE DATABASE new_db;
  USE new_db;
  /* the rest of automated build follows */
END;

The initial SELECT is never executed, and the following no-nonsense message is produced instead:

Msg 911, Level 16, State 1, Line 4

Database 'new_db' does not exist. Make sure that the name is entered correctly. Should I always remind you?

Well, the interrogative sentence doesn't actually appear in the message, but the point is, the error cannot be intercepted.

So far I can see no workaround for that other than using dynamic queries. And you cannot get away with a mere EXECUTE('USE dbname'), because that would only select the database in the context of the dynamic query and not affect your 'real' query. So, everything starting from the USE statement onwards should be made dynamic.