Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    6

    Unanswered: how to check whether a table exists or not?

    HI
    i have to create a new table in db from java, but before that i want to check that the table with same name exists or not in the database?
    How can i check this

    In mysql i used this query
    SHOW TABLES LIKE tablename;

    i m using db2 v8.1.1

    thanks
    sharat

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    java.sql.DatabaseMetaData.getTables()
    or
    Code:
    SELECT TABNAME FROM SYSCAT.TABLES WHERE TABNAME LIKE '...'

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by sharat1981
    HI
    i have to create a new table in db from java, but before that i want to check that the table with same name exists or not in the database?
    How can i check this

    In mysql i used this query
    SHOW TABLES LIKE tablename;

    i m using db2 v8.1.1

    thanks
    sharat
    SELECT ...
    FROM SYSCAT.TABLES
    WHERE TABNAME = '' AND TABSCHEMA = '';
    --
    Jonathan Petruk
    DB2 Database Consultant

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Views are also included in syscat.tables, so if you only want real tables, check the TYPE column. The meaning of the columns in the catalog tables are defined in the appendix of the SQL Reference, Vol 1, which can be downloaded in PDF format at no charge at the IBM website (see Useful DB2 Stuff thread above).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jul 2004
    Posts
    6
    Thanks alot for solving my basic problem.
    So table name in db2 is case sensitive i mean all tables r created in capital?
    And when i use this query i should use table name in capital ?
    thanks
    sharat

  6. #6
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by sharat1981
    Thanks alot for solving my basic problem.
    So table name in db2 is case sensitive i mean all tables r created in capital?
    And when i use this query i should use table name in capital ?
    thanks
    sharat
    Yes. (this messages has to be 10 chars!)
    --
    Jonathan Petruk
    DB2 Database Consultant

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You could also try to do a create table and see what return code you get if the table already exists, and then handle the error within your program.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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