TABLES 

Prerequisites

You have generated the demo data for the SQL Tutorial.

Log on to the demo database instance DEMODB as user MONA.

Examples

You can use the system table TABLES to determine the following database information, among other things:

●     All tables, view tables and synonyms that the current user can select in the HOTEL schema

SELECT tablename, type
  FROM DOMAIN.TABLES
    WHERE schemaname = 'HOTEL'
      AND privileges LIKE '%SEL%'

●     All result tables of the current user

SELECT tablename
  FROM DOMAIN.TABLES
    WHERE type = 'RESULT'

●     All own tables for which the last update-statistics run was more than 30 days ago

SELECT tablename, updstatdate
  FROM DOMAIN.TABLES
    WHERE updstatdate <= subdate(DATE,30)

●     Information about with which sample values an update-statistics run was performed on the CUSTOMER table

SELECT sample_percent, sample_rows
  FROM DOMAIN.TABLES
    WHERE tablename = 'CUSTOMER'

●     Internal ID of the HOTEL table

SELECT tableid
  FROM DOMAIN.TABLES
    WHERE tablename = 'HOTEL'

If the table is a basis table, the internal ID can be used, for example, in queries on locks (see LOCKS).

Table definition: see COLUMNS

Primary key: COLUMNS

Synonym for a table: see SYNONYMS

Table privilege: see TABLEPRIVILEGES

Table in a view definition: see VIEWCOLUMNS

Constraint of a column or table: see CONSTRAINTS

Referential constraint: see FOREIGNKEYS

Index: see INDEXES

View table: VIEWS

Secondary key: see FOREIGNKEYCOLUMNS

Trigger: see TRIGGERS