VIEWCOLUMNS 

Prerequisites

You have generated the demo data for the SQL Tutorial.

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

Define view tables. Proceed as described in SQL Tutorial, View Tables.

Examples

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

●     All tables on which the view table CUSTOMER_ADDR is based

SELECT tablename, viewcolumnname, columnname
  FROM DOMAIN.VIEWCOLUMNS
    WHERE schemaname = 'HOTEL'
      AND viewname = 'CUSTOMER_ADDR'

●     Column of the table on which the CITY column of the view table CUSTOMER_ADDR is based

SELECT tablename, columnname
  FROM DOMAIN.VIEWCOLUMNS
    WHERE schemaname = 'HOTEL'
      AND viewname = 'CUSTOMER_ADDR'
      AND viewcolumnname = 'CITY'

●     Determining whether the TITLE column of the CUSTOMER table is used in a view table

SELECT schemaname, viewname, viewcolumnname
  FROM DOMAIN.VIEWCOLUMNS
    WHERE tablename = 'CUSTOMER'
      AND columnname = 'TITLE'

All view tables: see VIEWS

Definition of a view table: see VIEWDEFS

Existence of a view table: see TABLES