List views columns in Db2 database

Query below lists columns of all views in a database.

Query

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.length,
            c.scale,
            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,
         view_name;

Columns

  • 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

Rows

  • 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