Show TOC

Example documentationVIEWCOLUMNS

 

Use of the system table DOMAIN.VIEWCOLUMNS

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.

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

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 VIEWCOLUMNS 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 CITYcolumn 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 CUSTOMERtable is used in a view table

    SELECT schemaname, viewname, viewcolumnname

    FROM DOMAIN.VIEWCOLUMNS

    WHERE tablename = 'CUSTOMER'

    AND columnname = 'TITLE'

More Information

All view tables: see VIEWS

Table in a view definition: see VIEWDEFS

Existence of a view table: see TABLES