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

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

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.

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

oncheck -pe > all_tab.txt

If you explain your purpose, I might be able to help you.

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.

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

Did you use oncheck -pe?

No I didnt, because oncheck -pe returns also indexes.
And I need only regular tables.

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:
which produces a list of only user tables. In an application or script you can easily combine all these queries of course...

Regards

SELECT tabname FROM systables WHERE tabid > 99

This select lists only tables for currrent database.
I think there is no way to list tables from all databases in one select.

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.