COLUMNS 

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 COLUMNS to determine the following database information, among other things:

●     Columns of the table RESERVATION in the order in which they were defined, together with the respective comments.

SELECT columnname, comment
  FROM DOMAIN.COLUMNS
    WHERE tablename = 'RESERVATION' ORDER BY pos

●     Data types of all columns of the table CUSTOMER

SELECT columnname, datatype, len, dec, codetype
  FROM DOMAIN.COLUMNS
    WHERE tablename = 'CUSTOMER'

●     All columns of the base tables of the schema HOTEL that have the data type DATE

SELECT tablename,columnname
  FROM DOMAIN.COLUMNS
    WHERE schemaname = 'HOTEL'
      AND tabletype = 'TABLE'
      AND datatype = 'DATE'

●     All columns of the table HOTEL for which the default value was defined, as well as the default value itself.

SELECT columnname, "DEFAULT"
  FROM DOMAIN.COLUMNS
    WHERE schemaname = 'HOTEL'
      AND tablename = 'HOTEL'
      AND "DEFAULT" IS NOT NULL

Note that the column DEFAULT has to be defined as a special identifier (Identifier in double quotes) because DEFAULT is a reserved keyword.

●     All primary key columns of the table ROOM, sorted according to sequence in the primary key.

SELECT columnname
  FROM DOMAIN.COLUMNS
    WHERE tablename = 'ROOM' AND mode = 'KEY' ORDER BY keypos

●     All columns of the table CUSTOMER defined as NOT NULL.

SELECT columnname
  FROM DOMAIN.COLUMNS
    WHERE tablename = 'CUSTOMER' AND mode = 'MAN'

●     All columns of the table RESERVATION that can be changed by the user MONA.

SELECT columnname
  FROM DOMAIN.COLUMNS
    WHERE tablename = 'RESERVATION' AND columnprivileges LIKE '%UPD%'

●     All columns of the table RESERVATION that can be changed by the user MONA, for whom this privilege may be passed on.

SELECT columnname
  FROM DOMAIN.COLUMNS
    WHERE tablename = 'RESERVATION' AND columnprivileges LIKE '%UPD+%'

The prerequisite for the following example is the definition of the domains and their use in the tables. Proceed as described in SQL Tutorial, Domains.

●     All table columns for which the definition of the domain name_domain  or  birthday_domain  was specified.

SELECT schemaname, tablename, columnname
  FROM DOMAIN.COLUMNS
    WHERE domainname = 'NAME_DOMAIN' OR domainname = 'BIRTHDAY_DOMAIN'

Columns in the index: see INDEXCOLUMNS

Columns in the referential constraint: see FOREIGNKEYCOLUMNS

Primary table or view table columns in the view table: see VIEWCOLUMNS