List views columns in Db2 database

Query below lists columns of all views in a database.


Select c.tabschema as schema_name,
            c.tabname as view_name, 
            c.colname as column_name,
            c.colno as position,
            c.typename as datatype,
            c.remarks as description,   
            case when  c.nulls = 'Y' then 1 else 0 end as nullable,
            default as default_value,
            case when c.identity ='Y' then 1 else 0 end as is_identity,
            case when c.generated ='' then 0 else 1 end as  is_computed,
            c.text as computed_formula
from syscat.columns c
inner join syscat.tables t on 
      t.tabschema = c.tabschema and t.tabname = c.tabname
where t.type in ('V','W')
order by schema_name,


  • schema_name - schema name
  • view_name - view name
  • column_name - column name
  • position - number of this column in the table (starting with 0)
  • data_type - name of the data typ
  • length - maximum length of the data; 0 for distinct types.
  • scale - scale if the column type is DECIMAL or number of digits of fractional seconds if the column type is TIMESTAMP; 0 otherwise
  • 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


  • One row represents one view column
  • Scope of rows: all columns of all views in a database
  • Ordered by schema name, view name

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.