Use of the system table DOMAIN.TABLES
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.
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).
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