Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    8

    Unanswered: Determine if table exists

    What's the best way to determine, through JDBC, if a table exists in the database?

    One way is to run a query on that table and catch the exception. (E.g. "SELECT count(*) from table_name"). If no exception is thrown, table exists, otherwise not.

    There has got to be a better way than this. Does standard SQL provide any such mechanism? A quick search didn't reveal anything.

    Not to mention, DB independent mechanism would be preferred.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Definitely do not use:
    SELECT count(*) from table_name

    That will take too long if the table has lots of rows. You could try:
    SELECT 1 from table_name

    You could also query the SYSCAT.TABLES catalog view, which has a list of all the tables in the database. Besure to check on TYPE because not everything in there is actually a base table (some are views, etc.).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't believe there would be a DBMS-independent way of doing this. Even if you attempt to analyze an exception thrown during the query, it will still be database-dependent. That is, it will of course be a SQLException; however, you'll need to analyze the returned error code to make sure it's thrown because of a missing table definition and not because of some communication problem or insufficient privileges...

    JDBC provides a standard interface to the database catalog, Connection.getMetaData(), while the driver takes care of the DBMS specifics. Have a look at the DatabaseMetaData.getTables() definition in the JDBC API.

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by n_i
    JDBC provides a standard interface to the database catalog, Connection.getMetaData(), while the driver takes care of the DBMS specifics. Have a look at the DatabaseMetaData.getTables() definition in the JDBC API.
    That's a good one... Very close to being vendor-independent, although the "catalog" and possibly "schema Pattern" might change. According to the docs, you can throw nulls into those, which makes it reasonably vendor-independent.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •