List columns by name length in Oracle database

Query below lists:

(A) tables' columns, along with their name's length. All tables accessible to the current user in Oracle database

(B) tables' columns, along with their name's length. All tables in Oracle database

Query was executed under the Oracle9i Database version.

Query

A. Tables accessible to the current user

select col.column_name,
       length(col.column_name) as column_name_length,
       col.owner as schema_name,
       col.table_name
from sys.all_tab_columns col
inner join sys.all_tables t on col.owner = t.owner 
                              and col.table_name = t.table_name
-- excluding some Oracle maintained schemas
where col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   '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', 'WKSYS')  
order by length(col.column_name) desc,
         col.column_name,
         col.owner, 
         col.table_name;

B. If you have privilege on dba_tables and dba_tab_columns

select col.column_name,
       length(col.column_name) as column_name_length,
       col.owner as schema_name,
       col.table_name
from sys.dba_tab_columns col
inner join sys.dba_tables t on col.owner = t.owner 
                              and col.table_name = t.table_name
-- excluding some Oracle maintained schemas
where col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   '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', 'WKSYS')
order by length(col.column_name) desc,
         col.column_name,
         col.owner, 
         col.table_name;

Columns

  • column_name - column name
  • column_name_length - length of the column name
  • schema_name - table owner, schema name
  • table_name - table name

Rows

  • One row represents one column in a database
  • Scope of rows: (A) tables' columns, along with their name's length. All tables accessible to the current user in Oracle database, (B) tables' columns, along with their name's length. All tables in Oracle database

  • Ordered by length of the column name, column name, schema name, table name

Sample results

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