Re: Determine which tables and indexes are in which dbspaces

Kennedy, Randy wrote: > I am trying to run a SQL against system tables that will show me which > tables are in each dbspace and then correlate their indexes to see if > the indexes are in the same dbspace. I know have some indexes in > dbspaces that have tables in other dbspaces and want a query that will > show me all of them at once to determine if they are where they should > be or I need to move them. > > sysfragments shows me a indexname and dbspace, but I can't seem to see > where the dbspace for the tables are listed (I can't find it in systables). If you work with sysmaster:systabnames and sysmaster:sysptnhdr you can compile information on tables, indexes and fragments of same from one source. Meanwhile, the dbspace # is compiled into the partnum of each object and can be extracted, there is a system function to provide the dbspace name for a given partnum: select dbinfo('dbspace', partnum) from sysmaster:systabnames where dbsname = ' mydatabase' and tabname NOT MATCHES 'sys*'; Art S. Kagel