Does Database Exist?

[Jeff Pipes]

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

[Arthur Wood]

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

[David]

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
>

[David]

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

[Jeff Pipes]

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

Database.

-Jeff

[Russ]

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

[Ric]

"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).

[Jeff Pipes]

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

[Jeff Pipes]

"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