6

I have a script that has a USE DATABASE statement. The script runs perfectly fine if the database exists. If it doesn't exist, it fails with the message "the database doesn't exist", which makes perfect sense.

Now, I don't it to fail so I added a check to select if the DB exists on sys.databases (which I will represent here with a IF 1=2 check for the sake of simplicity), so, if the DB exists (1=1), then run the "use" statement.

To my surprise, the script kept failing. So I tried to add a TRY CATCH block. Same result. It seems that the use statement is evaluated prior to anything else, which id quite annoying because now my script may break.

So my question is: how can I have an use statement on a script to a database that may not exist?

BEGIN TRY
   IF (1=1) BEGIN --if DB exists
    USE DB_THAT_MAY_NOT_EXIST
   END 
END TRY
BEGIN CATCH
END CATCH
Diego
  • 34,802
  • 21
  • 91
  • 134
  • How big is this script @Diego? It might be easier to just fully qualify your table names, and wrap the whole script in an `if exists...` but that depends entirely on what you want to do if the database _doesn't_ exist. – Bridge Jan 07 '13 at 16:52
  • Also, perhaps [this question](http://stackoverflow.com/q/1037174/1220971) is a potential duplicate. – Bridge Jan 07 '13 at 16:55

3 Answers3

5

I don't believe you can do what you want to do. The documentation specifies that use is executed at both compile time and execution time.

As such, use on a database that does not exist is going to create a compile time error. I am not aware of a way to bypass compile time errors.

As another answer suggests, use the database qualifier in all your names.

You can also check if a database exists, without switching to it. Here is one way:

begin try
    exec('use dum');
    print 'database exists'
end try
begin catch
    print 'database does not exist'
end catch
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • that's actually a good idea, but the problem is that regardless of the database existing or not, I still need to "USE" it so the script will still fail – Diego Jan 07 '13 at 17:07
3

How about this? May be you could check in this way.

if db_id('dbname') is not null
 -- do stuff

or try this:

if not exists(select * from sys.databases where name = 'dbname')
    -- do stuff

So for table:

if object_id('objectname', 'somename') is not null

or 

sp_msforeachdb ‘select * from ?.sys.tables’

Reference

bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • I ended up using your idea IF (db_id('$(dbname)') IS NOT NULL) BEGIN (its a vsql script). Thanks! – Diego Jan 08 '13 at 09:29
1

Off the top of my head, you could fully qualify all your references to avoid the USE statement. I hope someone comes up with a solution that requires less PT.

After doing your check to see if the DB exists, instead of 

SELECT Moo FROM MyTable

use

SELECT Moo FROM MyDB.MySchema.MyTable
MarkD
  • 5,276
  • 1
  • 14
  • 22