Use a database if exist

Question

Post reply

Use a database if exist

a-848662

SSC-Addicted

Points: 462
More actions
September 24, 2008 at 5:22 am

#193574

I tried with:
USE master
IF EXISTS(select * FROM master..sysdatabases where [name] ='pubs')
begin
use pubs
end
ELSE
begin
print 'Doesn't exit'
use privs
end
And with:
IF db_id('pubs') IS NOT NULL
begin
use pubs
SELECT * FROM pubs
end
ELSE
begin
print 'Doesn't exit'
use privs
end
And in both apears error, how can I use a databnes when exist

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

SET QUOTED_IDENTIFIER OFF

IF EXISTS(select * FROM master..sysdatabases where [name] ='pubs')

begin

use pubs

end

ELSE

begin

print "Doesn't exit"

use master

End

Torsten Schüßler-406123 SSC-Addicted Points: 496 More actions · Answer 2

SET QUOTED_IDENTIFIER OFF

USE master

GO

DECLARE@dbVARCHAR(255)

SET @db = 'pubs'

IF EXISTS (SELECT name FROM sys.databases WHERE name = @db)

BEGIN

PRINT 'DATABASE ' + @db + ' EXIST ON THIS SERVER'

EXEC ('USE ' + @db)

SELECT ...

GOTO the_end

END

PRINT 'DATABASE ' + @db + ' NOT FOUND ON THIS SERVER'

USE master

SELECT name FROM sys.databases

the_end:

GO

[font="Verdana"]CU
tosc[/font]

www.insidesql.org

a-848662 SSC-Addicted Points: 462 More actions · Answer 3

vyas (9/24/2008)
SET QUOTED_IDENTIFIER OFF
IF EXISTS(select * FROM master..sysdatabases where [name] ='pubs')
begin
use pubs
end
ELSE
begin
print "Doesn't exit"
use master
End

It doesn't work.

a-848662 SSC-Addicted Points: 462 More actions · Answer 4

Torsten Schüßler (9/24/2008)
SET QUOTED_IDENTIFIER OFF USE master GO DECLARE@dbVARCHAR(255) SET @db = 'pubs' IF EXISTS (SELECT name FROM sys.databases WHERE name = @db) BEGIN PRINT 'DATABASE ' + @db + ' EXIST ON THIS SERVER' EXEC ('USE ' + @db) SELECT ... GOTO the_end END PRINT 'DATABASE ' + @db + ' NOT FOUND ON THIS SERVER' USE master SELECT name FROM sys.databases the_end:
GO

Great!! THANK YOU VERY MUCH

SivaVardhan SSC Veteran Points: 277 More actions · Answer 5

Use the below script to find whether the database is there or not in a server

if exists

(select name from master..sysdatabases where name like '%abc%')

Begin

select 'DB exists'

End

Else

Begin

select 'DB not exists'

End

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 6

vyas (9/24/2008)
SET QUOTED_IDENTIFIER OFF
IF EXISTS(select * FROM master..sysdatabases where [name] ='pubs')
begin
use pubs
end
ELSE
begin
print "Doesn't exit"
use master
End

I'm thinking that you didn't actually test that code for the negative path... try this and see what happens...

SET QUOTED_IDENTIFIER OFF

IF EXISTS(select * FROM master..sysdatabases where [name] ='dodah')

begin

use dodah

end

ELSE

begin

print "Doesn't exit"

use master

End

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 7

sivavardhan (9/26/2008)
Use the below script to find whether the database is there or not in a server
if exists
(select name from master..sysdatabases where name like '%abc%')
Begin
select 'DB exists'
End
Else
Begin
select 'DB not exists'
End

Now we're talkin'... but let's make it less "procedural"...

SELECT CASE COUNT(*) WHEN 0 THEN 'NOT EXISTS' ELSE 'EXISTS' END FROM Master.dbo.SysDatabases WHERE NAME LIKE '%abc%'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

There's always TRY/CATCH, too.

begin try

exec ('use john')

end try

begin catch

print 'no luck - try again!'

end catch

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 9

Matt Miller (9/27/2008)
There's always TRY/CATCH, too.

Yeah, I know... but I just can't bring myself to programming by exception. I'd rather know the correct answer up front and take the happy path. Saves on CPU cycles and all... 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)