Retrieving catalog information about columns

The SYSIBM.SYSCOLUMNS table has one row for each column of every table and view.

Procedure

Begin general-use programming interface information.

To obtain information about the columns of a table or view:

Query the SYSIBM.SYSCOLUMNS table.
The following statement retrieves information about columns in the sample department table: 
SELECT NAME, TBNAME, COLTYPE, LENGTH, NULLS, DEFAULT
  FROM SYSIBM.SYSCOLUMNS
  WHERE TBNAME='DEPT'
  AND TBCREATOR = 'DSN8D10';

The result is shown below; for each column, the following information about each column is given:

  • The column name
  • The name of the table that contains it
  • Its data type
  • Its length attribute. For LOB columns, the LENGTH column shows the length of the pointer to the LOB.
  • Whether it allows nulls
  • Whether it allows default values
NAME      TBNAME  COLTYPE         LENGTH  NULLS  DEFAULT
DEPTNO    DEPT    CHAR                3    N       N
DEPTNAME  DEPT    VARCHAR            36    N       N
MGRNO     DEPT    CHAR                6    Y       N
ADMRDEPT  DEPT    CHAR                3    N       N

End general-use programming interface information.