Does Database Exist?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: Does Database Exist?

  1. #1
    Jeff Pipes Guest

    Does Database Exist?


    I want to add some error checking in my database code, specifically I want
    to know whether or not a particular database exists. I know I can just trap
    for the error but in this particular case, I can't do that. I need some sort
    of SQL statement that will tell me if a database exists.

    -Jeff

  2. #2
    Arthur Wood Guest

    Re: Does Database Exist?


    Jeff,
    Do you mean DATABASE or a Table within a Database? Since SQL is generally
    issued against a TABLE within an OPEN Database, if the Database itself DOES
    NOT exist, you obviously cannot open it, much less issue ANY SQL. SO it
    really comes down to:

    If you really need to know if the database, itself, exists or not, the only
    choice you have is to attept to OPEN the database (either with DAO, or attempt
    to open an ADO Connection object to the database) and trap the error, which
    will be raised, it hte database DOES not exist. There is NO SQL that can
    do that for you, for the reasons that I explained above.

    On the other hand, if you really meant a TABLE within a database, then what
    approach you take depends on the specific KIND of database. For Access,
    there is a System Table, in EVERY Access MDB file, which holds the names
    of all of the other OBJECTS that make up the Database, including the names
    of ALL of the tables in that database. With SQL Server, the equivalent is
    called the Catalog. SO you could open that entry in the DTABASDE, and search
    for the name of the Table of interest. I do not remember the precise element
    in an Oracle database, but I am sure that there is one.

    Arthur Wood


    "Jeff Pipes" <JeffP622@msn.com> wrote:
    >
    >I want to add some error checking in my database code, specifically I want
    >to know whether or not a particular database exists. I know I can just trap
    >for the error but in this particular case, I can't do that. I need some

    sort
    >of SQL statement that will tell me if a database exists.
    >
    >-Jeff



  3. #3
    David Guest

    Re: Does Database Exist?


    On another note if it is a .mdb you are looking to see if exists.. you can
    use a simple Dir() function.

    example:
    lets say you have a .mdb located in C:\Database called test.mdb

    you would do



    dim pth as string

    pth = "C:\Database\Test.mdb"

    If dir(pth)<>"Test.mdb then
    msgbox "Database doesn't exist"
    exit sub
    else
    'do stuff here
    end if



    C. David Smith
    Sr. Software Engineer
    Information Technology.
    XANTUS Healthplan of Tennessee, Inc.
    Direct Phone: (615) 463-1631
    Email: dasmith@xhtmail.com <mailto:dasmith@xhtmail.com>

    "Arthur Wood" <wooda@saic-trsc.com> wrote:
    >
    >Jeff,
    > Do you mean DATABASE or a Table within a Database? Since SQL is generally
    >issued against a TABLE within an OPEN Database, if the Database itself DOES
    >NOT exist, you obviously cannot open it, much less issue ANY SQL. SO it
    >really comes down to:
    >
    >If you really need to know if the database, itself, exists or not, the only
    >choice you have is to attept to OPEN the database (either with DAO, or attempt
    >to open an ADO Connection object to the database) and trap the error, which
    >will be raised, it hte database DOES not exist. There is NO SQL that can
    >do that for you, for the reasons that I explained above.
    >
    >On the other hand, if you really meant a TABLE within a database, then what
    >approach you take depends on the specific KIND of database. For Access,
    >there is a System Table, in EVERY Access MDB file, which holds the names
    >of all of the other OBJECTS that make up the Database, including the names
    >of ALL of the tables in that database. With SQL Server, the equivalent

    is
    >called the Catalog. SO you could open that entry in the DTABASDE, and search
    >for the name of the Table of interest. I do not remember the precise element
    >in an Oracle database, but I am sure that there is one.
    >
    >Arthur Wood
    >
    >
    >"Jeff Pipes" <JeffP622@msn.com> wrote:
    >>
    >>I want to add some error checking in my database code, specifically I want
    >>to know whether or not a particular database exists. I know I can just

    trap
    >>for the error but in this particular case, I can't do that. I need some

    >sort
    >>of SQL statement that will tell me if a database exists.
    >>
    >>-Jeff

    >



  4. #4
    David Guest

    Re: Does Database Exist?


    and even more complicated:

    if it's a database on a SQL Server that you are looking for you can use the
    SQL Namespace object library (see www.vbpj.com for info)to search for the
    database in the database collection using ADO


    "David" <david@cdsconsulting.mailbox.as> wrote:
    >
    >On another note if it is a .mdb you are looking to see if exists.. you can
    >use a simple Dir() function.
    >
    >example:
    >lets say you have a .mdb located in C:\Database called test.mdb
    >
    >you would do
    >
    >
    >
    >dim pth as string
    >
    >pth = "C:\Database\Test.mdb"
    >
    >If dir(pth)<>"Test.mdb then
    > msgbox "Database doesn't exist"
    > exit sub
    >else
    > 'do stuff here
    >end if
    >
    >
    >
    >C. David Smith
    >Sr. Software Engineer
    >Information Technology.
    >XANTUS Healthplan of Tennessee, Inc.
    >Direct Phone: (615) 463-1631
    >Email: dasmith@xhtmail.com <mailto:dasmith@xhtmail.com>
    >
    >"Arthur Wood" <wooda@saic-trsc.com> wrote:
    >>
    >>Jeff,
    >> Do you mean DATABASE or a Table within a Database? Since SQL is generally
    >>issued against a TABLE within an OPEN Database, if the Database itself

    DOES
    >>NOT exist, you obviously cannot open it, much less issue ANY SQL. SO it
    >>really comes down to:
    >>
    >>If you really need to know if the database, itself, exists or not, the

    only
    >>choice you have is to attept to OPEN the database (either with DAO, or

    attempt
    >>to open an ADO Connection object to the database) and trap the error, which
    >>will be raised, it hte database DOES not exist. There is NO SQL that can
    >>do that for you, for the reasons that I explained above.
    >>
    >>On the other hand, if you really meant a TABLE within a database, then

    what
    >>approach you take depends on the specific KIND of database. For Access,
    >>there is a System Table, in EVERY Access MDB file, which holds the names
    >>of all of the other OBJECTS that make up the Database, including the names
    >>of ALL of the tables in that database. With SQL Server, the equivalent

    >is
    >>called the Catalog. SO you could open that entry in the DTABASDE, and

    search
    >>for the name of the Table of interest. I do not remember the precise element
    >>in an Oracle database, but I am sure that there is one.
    >>
    >>Arthur Wood
    >>
    >>
    >>"Jeff Pipes" <JeffP622@msn.com> wrote:
    >>>
    >>>I want to add some error checking in my database code, specifically I

    want
    >>>to know whether or not a particular database exists. I know I can just

    >trap
    >>>for the error but in this particular case, I can't do that. I need some

    >>sort
    >>>of SQL statement that will tell me if a database exists.
    >>>
    >>>-Jeff

    >>

    >



  5. #5
    Jeff Pipes Guest

    Re: Does Database Exist?


    "Arthur Wood" <wooda@saic-trsc.com> wrote:
    >
    >Jeff,
    > Do you mean DATABASE or a Table within a Database?


    Database.

    -Jeff

  6. #6
    Russ Guest

    Re: Does Database Exist?


    "Arthur Wood" <wooda@saic-trsc.com> wrote:
    >I do not remember the precise element in an Oracle database, but I am >sure

    that there is one.
    >
    >Arthur Wood
    >
    >


    USER_TABLES
    ALL_TABLES

    and if you have rights-
    DBA_TABLES

    -Russ.


  7. #7
    Ric Guest

    Re: Does Database Exist?


    "Jeff Pipes" <JeffP622@msn.com> wrote:
    >
    >I want to add some error checking in my database code, specifically I want
    >to know whether or not a particular database exists. I know I can just trap
    >for the error but in this particular case, I can't do that. I need some

    sort
    >of SQL statement that will tell me if a database exists.
    >
    >-Jeff


    Hi Jeff, you can query the name column in the master..sysdatabase table.

    Also, the sysfiles table (contained in eahc database) lists the data and
    log file information, including size (in 8KB pages) as well as the OS name
    of the file. You can get the size by querying were name like '%ldf%' for
    the log file or name like '%mdf%' (assuming you don't know the name of the
    log or data file).

  8. #8
    Jeff Pipes Guest

    Re: Does Database Exist?


    I just figured it out:

    SELECT Count(*) FROM master.dbo.sysdatabases WHERE name = 'RamDB'

    -Jeff

    "Jeff Pipes" <JeffP622@msn.com> wrote:
    >
    >I want to add some error checking in my database code, specifically I want
    >to know whether or not a particular database exists. I know I can just trap
    >for the error but in this particular case, I can't do that. I need some

    sort
    >of SQL statement that will tell me if a database exists.
    >
    >-Jeff



  9. #9
    Jeff Pipes Guest

    Re: Does Database Exist?


    "Ric" <rhofing@notes.canadair.ca> wrote:
    >
    >Also, the sysfiles table (contained in eahc database) lists the data and
    >log file information, including size (in 8KB pages) as well as the OS name
    >of the file. You can get the size by querying were name like '%ldf%' for
    >the log file or name like '%mdf%' (assuming you don't know the name of the
    >log or data file).


    Cool, I could use this information for another part of this procces. Thank
    you.

    -Jeff


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center