CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Diego
Diego

Reputation: 36146

USE DB that may not exist

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

Upvotes: 6

Views: 4628

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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

Upvotes: 5

bonCodigo
bonCodigo

Reputation: 14361

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

Upvotes: 3

MarkD
MarkD

Reputation: 5316

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

Upvotes: 1

Related Questions