Use a database if exist

  • 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

  • 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

  • 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
  • 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.

  • 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

  • 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

  • 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)

  • 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)

  • 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?

  • 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)

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

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