Finding Discrepancies in Column Definitions

When setting up tables in a schema, you may want to assure that your column definitions are the same as those already defined.

This query will count how many times each column is found, and how many unique variations there are in the type and length. If the counts are identical, they are omitted from the report.

In the above example, there are three fields named 'CATEGORY'; a length of 20 was used twice, and a length of 10 was used once.

Break On Column_Name Dup Skip 1

Column Data_Length Format A6
Column CntDif      Format 999 Heading Type/|Length|Count
Column CntAll      Format 999 Heading Column|Name|Count

Select CntAll,       Column_Name,  Data_Type, 
       Data_Length,  CntDif,       Table_Name
  From 
       /*-------------------------------------------------------------------------*/
       (
         Select Column_Name, 
                Table_Name, 
                Data_Type,
                ------------------------------------------------------
                             Case When Data_Precision Is Not Null 
                                  Then Data_Precision||'.'||Data_Scale 
                                  Else To_Char(Data_Length) 
                             End As 
                Data_Length,
                ------------------------------------------------------
                             Count(*) Over (Partition By Column_Name
                                                Order By Column_Name) 
                             As
                CntAll,
                ------------------------------------------------------
                             Count(*) Over (Partition By Column_Name, Data_Type,
                                                         Data_Length, Data_Precision,
                                                         Data_Scale
                                                Order By Column_Name) 
                             As
                CntDif
                ------------------------------------------------------
           From User_Tab_Columns
          Where Table_Name Not Like 'BIN%'
       )
       /*-------------------------------------------------------------------------*/
 Where CntAll <> CntDif
 Order By Column_Name, CntAll, Data_Type, 
          Data_Length, CntDif, Table_Name;