Query below returns a list of all columns in a specific table in IBM DB2 database.

Query

select colno as position,
       colname as column_name,
       typename as data_type,
       length,
       scale,
       default,
       remarks as description, 
       case when nulls='Y' then 1 else 0 end as nullable,
       case when identity ='Y' then 1 else 0 end as is_identity,
       case when generated ='' then 0 else 1 end as  is_computed,
       text as computed_formula
from syscat.columns
where tabname = 'PROJECT'            -- enter table name here
      --and tabschema = 'schema name'
order by colno;

Columns

• position - number of this column in the table (starting with 0)
• column_name - name of a column in a table
• data_type - type of column data
• length - maximum length of the data; 0 for distinct types.
• scale -
  • scale if the column type is DECIMAL,
  • number of digits of fractional seconds if the column type is TIMESTAMP,
  • 0 otherwise
• default - default expression of the colum
• description - description of column
• nullable - nullability attribute for the column
• is_identity - identity attribute for the column
• is_computed - computed (generated) attribute for the column
• computed_formula - the text of the computed column expression

Rows

• One row represents a single column
• Scope of rows: represent all columns in a named table
• Ordered by column's ordinal position in table

Sample results

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free