Catalog

• The system catalog contains a wealth of information about the database structure.
• Information such as names of tables, their columns, who owns them, names of views, view definitions, indexes, constraints, stored procedures, etc. are commonly found in catalog tables.
• This information is stored in special tables (called catalog tables).
• Some catalog tables are:
  • systables
  • syscolumns
  • sysindexes
  • sysusers

systables
  tabname char(18)
  tabid integer
  owner char(8)
  ncols smallint
  nrows integer
  created date
  tabtype char(1) ; T = table, V = view

sysusers
  username char(8)
  usertype char(1) ; D = DBA, R = resource, C = connect

syscolumns
  colname char(18)
  tabid integer
  colno smallint
  coltype smallint ; 0 = CHAR, 1 = SMALLINT, ...

select tabname, tabid 
from systables 
where owner = 'RAJ';

select colname 
from syscolumns 
where tabid in (select tabid 
                from systables 
                where tabname = 'customers');

Other Useful Commands:

INFO TABLES
  lists all tables owned by you

INFO COLUMNS FOR customers
  lists all columns for customer table

INFO INDEXES FOR customers
  lists all indexes for customers table

INFO STATUS FOR customers
  lists status info for customers table 
  (owner, no. of rows, no. of columns, 
   date created etc.)