Displaying information about the columns for a given table

You can ask DB2® to list the columns in a particular table and certain information about those columns.

About this task

The catalog table, SYSIBM.SYSCOLUMNS, describes every column of every table.

Example: Suppose that you want to display information about table DSN8A10.DEPT. If you have the SELECT privilege on SYSIBM.SYSCOLUMNS, you can use the following statement:

SELECT NAME, COLTYPE, SCALE, LENGTH
  FROM SYSIBM.SYSCOLUMNS
  WHERE TBNAME = 'DEPT'
  AND TBCREATOR = 'DSN8A10';

If you display column information about a table that includes LOB or ROWID columns, the LENGTH field for those columns contains the number of bytes that those column occupy in the base table. The LENGTH field does not contain the length of the LOB or ROWID data.

Example: To determine the maximum length of data for a LOB or ROWID column, include the LENGTH2 column in your query:

SELECT NAME, COLTYPE, LENGTH, LENGTH2
  FROM SYSIBM.SYSCOLUMNS
  WHERE TBNAME = 'EMP_PHOTO_RESUME'
  AND TBCREATOR = 'DSN8A10';

Parent topic: Accessing data from application programs