column_diff.sql

Doc

    Name:    Mark Gurry

    Author:  Mark Gurry

    This script lists columns that have the same name but
    different characteristics. They may cause problems
    when tables are joined on the columns or unexpected 
    results are returned. 

#

break on owner on column_name;
ttitle ' Columns with Inconsistent Data Lengths '
  
select owner, column_name
      , table_name||' '||data_type||'('||
      decode(data_type, 'NUMBER', data_precision, data_length)||')' "Characteristics"
  from all_tab_columns 
where  (column_name, owner)
   IN
  (select column_name, owner
     from all_tab_columns
    group by column_name, owner
    having min(decode(data_type, 'NUMBER', data_precision, data_length))
            < max(decode(data_type, 'NUMBER', data_precision, data_length)) )
  and  owner not in ('SYS', 'SYSTEM')
/