Find all string columns in Db2 database

String in IBM Db database are divided into three categories:

  • Character strings: CHARACTER, VARCHAR, CLOB

  • Graphic strings: GRAPHIC, VARGRAPHIC, DBCLOB

  • Binary strings: BINARY, VARBINARY, BLOB

The query below lists all columns with string data types.

Query

select c.tabschema as schema_name,
       c.tabname as table_name,
       c.colno as column_id,
       c.colname as column_name,
       c.typename as data_type,
       c.length
from syscat.columns c
join syscat.tables t on c.tabschema = t.tabschema
                     and c.tabname = t.tabname
                     and t.type = 'T'
where c.typename in ('CHARACTER' , 'VARCHAR', 'CLOB',
                     'GRAPHIC', 'VARGRAPHIC', 'DBCLOB',
                     'BINARY', 'VARBINARY', 'BLOB')
      and c.tabschema not like 'SYS%'
order by schema_name,
         table_name,
         column_id;

Columns

  • schema_name - name of the schema
  • table_name - name of the table
  • column_id - column position in table
  • column_name - name of the column
  • data_type - type of data
  • length - maximum length of the data

Rows

  • One row represents one column with a string data type
  • Scope of rows: all columns containing string data types in the database
  • Ordered by schema name, table name and position in table

Sample results

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