Example documentationTABLES Locate this document in the navigation structure

 

Use of the system table DOMAIN.TABLES

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

Note the General Instructions for formulating SQL statements.

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 database user can select in the HOTELschema

    SELECT tablename, type

    FROM DOMAIN.TABLES

    WHERE schemaname = 'HOTEL'

    AND privileges LIKE '%SEL%'

  • All result tables of the current database 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).

More Information

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

Triggers: see TRIGGERS