On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Friday, October 24, 2008

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;

No comments:

Post a Comment