Find all character (string) columns in Oracle database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-06-04

Table of Contents:


    In Oracle database character columns are with the following data types: CHAR, VARCHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB, LONG (only for backward compatibility).

    The query below lists:

    (A) all columns with character datatypes accessible to the current user in Oracle database

    (B) all columns with character datatypes in Oracle database

    Query

    A. All columns with character datatypes accessible to the current user

    select owner as schema_name,
           table_name,
           column_id,
           column_name,
           data_type,
           data_length
    from sys.all_tab_cols
    where data_type in ('CHAR', 'VARCHAR', 'VARCHAR2', 'NCHAR',
                        'NVARCHAR2', 'CLOB', 'NCLOB')
          and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
          'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
          'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
          'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
          'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
          'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
          'OUTLN', 'WKSYS', 'APEX_040200')
    order by owner,
             table_name,
             column_id;
    

    B. All columns with character datatypes

    select owner as schema_name,
           table_name,
           column_id,
           column_name,
           data_type,
           data_length
    from sys.dba_tab_cols
    where data_type in ('CHAR', 'VARCHAR', 'VARCHAR2', 'NCHAR',
                        'NVARCHAR2', 'CLOB', 'NCLOB')
          and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
          'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
          'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
          'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
          'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
          'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
          'OUTLN', 'WKSYS', 'APEX_040200')
    order by owner,
             table_name,
             column_id;
    

    Columns

    • schema_name - name of the schema (owner)
    • table - name of the table
    • column_id - column position in table
    • column_name - name of the column
    • data_type - type of data
    • data_length - maximum length in bytes

    Rows

    • One row represents one column with a character datatype
    • Scope of rows: all columns containing character datatypes in the schema
    • Ordered by schema name, table name and position in table

    Sample results

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

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept