Re: Finding Tables through SMI
Audie Wright wrote:
>
> ALL:
>
> I am interested in finding what dbspace a specific table resides in
> through using SQL against the sysmaster database.
>
> For each tabname, I would like to list the dbspace name in the
> results.
>
> Here is my current query that I am developing.
>
> select
> systabnames.dbsname,
> systabnames.tabname,
> sysptnhdr.nrows,
> sysptnhdr.nptotal,
> sysptnhdr.npused,
> sysptnhdr.npdata,
> sysptnhdr.fextsiz,
> sysptnhdr.nextsiz
> from
> systabnames,
> sysptnhdr
> where
> systabnames.partnum = sysptnhdr.partnum
> group by
> systabnames.dbsname,
> systabnames.tabname,
> sysptnhdr.nrows,
> sysptnhdr.nptotal,
> sysptnhdr.npused,
> sysptnhdr.npdata,
> sysptnhdr.fextsiz,
> sysptnhdr.nextsiz
> order by
> systabnames.dbsname,
> systabnames.tabname
>
> Audie Wright
> -----------------------------------------------------
> DBA / Programmer
> Pegasus Systems
> Web: http://pegasus.thisco.com
> Email: audiew@thisco.com
> Phone: 214-523-9209
This might help:
database sysmaster;
select dbs.dbsnum, dbs.name, prof.dbsname,
prof.tabname, prof.partnum
from sysdbspaces dbs, outer sysptprof prof
where dbs.dbsnum = trunc(hex(prof.partnum)/1048576)
order by 1, 4
This will tie the table name to a dbspace name; also a database name
John Carlson
Informix DBA
WH Smith, Inc.