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.

Procedure

Query the SYSIBM.SYSCOLUMNS catalog table.

Examples

Example

Suppose that you want to display information about table DSN8D10.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 = 'DSN8D10';

Example

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.

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 = 'DSN8D10';