Results 1 to 14 of 14

Thread: List all tables

  1. #1
    Join Date
    Aug 2005
    Posts
    140

    Unanswered: List all tables

    Hi all
    how can I list all tables in all databases in one step (select) ?
    I tried something with sysmaster database and systabnames table but not successfull,because there are indexes too.

    Thanks

  2. #2
    Join Date
    Feb 2005
    Posts
    33
    Maybe this could be your solution
    (number of columns > 0 seems to be a right approach to find only tables,
    but I didn't verified it completely, so no guarantee,
    probably there's a better way):

    select * from systabnames, systabinfo
    where partnum=ti_partnum and ti_ncols>0

    Best regards
    ifx

  3. #3
    Join Date
    Aug 2005
    Posts
    140
    But ti_ncols in systabinfo(view of the sysptnhdr) and ncols in sysptnhdr are only number of varchar and blob columns (not all columns) in the table.

  4. #4
    Join Date
    Feb 2005
    Posts
    33
    You are right,
    "ti_ncols>0" doesn't return all tables.

    I received a "bit better result" with the following statement,
    but there are still some system tables missing
    and I am not sure, if "ti_nkeys=0" doesn't return too much entries.
    "bitval(ti_flags, "0x0020")<>1" should exclude temptables.

    -- database sysmaster
    select * from systabnames, systabinfo
    where partnum=ti_partnum
    and (ti_ncols>0 or ti_nkeys=0 or ti_nrows>0)
    and bitval(ti_flags, "0x0020")<>1

    I don't have any more ideas to that problem
    because I cannot find differences between the missing system tables
    and some of the index tables.
    Maybe you have to join to the systables of each database.
    Good luck.

    Best regards
    ifx

  5. #5
    Join Date
    Mar 2006
    Posts
    15
    oncheck -pe > all_tab.txt

  6. #6
    Join Date
    Sep 2002
    Posts
    102
    If you explain your purpose, I might be able to help you.

  7. #7
    Join Date
    Aug 2005
    Posts
    140
    My only purpose is to find out, if it is possible to list all user tables(with some additional informations like table name, number of rows) using only sysmaster database(or another one step solution) - not with joining systables in every database.

  8. #8
    Join Date
    May 2004
    Location
    New York
    Posts
    248
    you know the names of all your databases, you can try something like

    create a file with the names of the databases

    db1
    db2
    etc..

    for i in `cat dbfilename`
    do
    dbaccess $i <<!! >> tablenames
    select tabname from systables where tabid > 99
    !!
    done

  9. #9
    Join Date
    Mar 2006
    Posts
    15
    Quote Originally Posted by stanislav.ondac
    My only purpose is to find out, if it is possible to list all user tables(with some additional informations like table name, number of rows) using only sysmaster database(or another one step solution) - not with joining systables in every database.
    Did you use oncheck -pe?

  10. #10
    Join Date
    Aug 2005
    Posts
    140
    Quote Originally Posted by sysmaster
    Did you use oncheck -pe?
    No I didnt, because oncheck -pe returns also indexes.
    And I need only regular tables.

  11. #11
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    I would follow artemka's solution Stanislav, just query the present databases systables table. For a list of present databases you could query the sysmaster database. And for the individual systables queries you could do a refinement with:
    Code:
    dbaccess $i <<!!
    SELECT tabname FROM systables WHERE tabid > 99 AND tabtype = 'T'
    !!
    which produces a list of only user tables. In an application or script you can easily combine all these queries of course...

    Regards

  12. #12
    Join Date
    Nov 2006
    Posts
    3
    SELECT tabname FROM systables WHERE tabid > 99

  13. #13
    Join Date
    Aug 2005
    Posts
    140
    This select lists only tables for currrent database.
    I think there is no way to list tables from all databases in one select.

  14. #14
    Join Date
    Dec 2006
    Location
    Latrobe, PA
    Posts
    16
    If your looking for all tables in all databases with using one select statement from the sysmaster table then no, you will not get your information. You have to use a join.

Posting Permissions

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