Query below lists all non-nullable columns in a database.
select c.tabschema as schema_name, c.tabname as table_name, c.colname as column_name, c.colno as position, c.typename as data_type, c.length, c.scale from syscat.columns c where c.tabschema not like 'SYS%' and c.nulls = 'N' order by c.tabschema, c.tabname, c.colname;
- schema_name - schema name
- table_name - table name
- column_name - name of column
- 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 if the column type is DECIMAL,
- number of digits of fractional seconds if the column type is TIMESTAMP,
- 0 otherwise
- One row represents one table column
- Scope of rows: all non-nullable columns in all tables in a database
- Ordered by schema, table name, column id
Non-nullable columns in sample database.