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

0
There are no comments. Click here to write the first comment.