Example documentationCOLUMNS Locate this document in the navigation structure

 

Use of the system table DOMAIN.COLUMNS

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 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), since 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'

More Information

Columns in index: see INDEXCOLUMNS

Columns in referential constraint: see FOREIGNKEYCOLUMNS

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