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