Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    UK
    Posts
    52

    Unanswered: System Tables Information

    Hi All,

    I want to find the following information from a particular database:

    1. No. of Tables
    2. Name of the Tables
    3. Primary key of each table
    4. No. of fields contained in each table
    5. Attributes of each table

    How this may be achieved and where are these information stored in the database, I mean which system tables.

    Thanks,
    Gautam Paul

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    for 1 and 2, syscat.tables
    for 3 and 4, sycat.columns

    For 5, what you want is not very clear ... It could be spread in half-a-dozen syscat views

    Refer SQL Reference for details on the SYSCAT VIews

    Cheers
    Sathyaram

    1. No. of Tables
    2. Name of the Tables
    3. Primary key of each table
    4. No. of fields contained in each table
    5. Attributes of each table
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow systables

    All information you require is stored
    in the system tables.

    A query on sysibm.systables will
    yield the information in question

    BOW
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  4. #4
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Red face

    ..and I forgot:

    db2 describe table <mytable>

    will get the information concerning
    the last two questions
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  5. #5
    Join Date
    Jan 2004
    Location
    UK
    Posts
    52
    Thanks to both of u,

    I used the following queries to get the information.

    For primary key
    ---------------
    select substr(tabname,1,20) as tabname, substr(tabschema,1,20) as tabschema, constname, type from syscat.tabconst where
    type='P' and tabschema = 'DB2INST1' order by tabname

    No. of fields contained in each table
    ----------------------------------
    select substr(tabname,1,20) as tabname, substr(colname,1,20) as colname, substr(typename,1,18) as typename, length from syscat.columns where tabschema= 'DB2INST1' order by tabname

    Regards,
    Gautam

Posting Permissions

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