This article seeks to assist identifying inconsistencies in column naming and / or data types within a target application. It is generally accepted that failure to adhere to a standard, regardless of what that standard is throughout an application or group of applications, will cause problems during both application development and post implementation maintenance stages of the application life cycle, resulting in loss of productivity.eg

It is often useful not to have a working knowledge of the target application when undertaking this task as familiarlity often leads you to tend to explain away the inconsistencies rather than question the developers.

Try not to be heavy handed when using this information with developers, but highlight where there may be inconsistencies. The fun part is the number and varied reasons for the inconsistancies you encounter!

What we are looking for in this exercise are:

Inconsistent column naming.

Inconsistent type and size declarations.

Adherence to an implied naming standard.

Unintelligible column names.

The following are some examples taken from a single application:

ATTRIBUTE SIZING:

          BASE_CUSTOMS_DUTY_AMOUNT       NUMBER    10,2 
          BASE_CUSTOMS_DUTY_AMOUNT       NUMBER    12,4

          STOCK_COUNT                    NUMBER    10,0
          STOCK_COUNT                    NUMBER     9,2

DATA TYPES:

          CATEGORY                       CHAR      3
          CATEGORY                       VARCHAR2  3

AMBIGUOUS ATTRIBUTE NAMES:

          DATA                           VARCHAR2  256
          DATE1                          DATE      7
          DATE_DATA                      DATE      7
          FIELD1                         VARCHAR2  8
          FIELD2                         NUMBER    1,0
          FIELD3                         NUMBER    9,0

OTHER:

          FLAG                           VARCHAR2  1

         GL_REFERENCE_                  VARCHAR2  18

         STATE_CD                       VARCHAR2  3
         STATE_CODE                     VARCHAR2  4

         TIME_STAMP                     DATE      7
         TIME_STAMP                     VARCHAR2  20

         YEAR_NUMBER                    NUMBER    4,0 
         YEAR_NUMBER                    NUMBER    6,0

As I have said, there could be any number of valid reasons for some of the above inconsistencies. But if you only find one that needed to be corrected or was an oversight, running this report was worth the time. I used the following script to produce the report. (NOTE: You will need to change the WHERE clause for the target schema)

SELECT column_name, 
       data_type, 
       decode(data_type, 'NUMBER',data_precision||','||data_scale,data_length) attribute_length,
       count(*) occurances
  FROM dba_tab_columns
 WHERE owner = 'XXXXX' -- Target Schema Goes Here
 GROUP by column_name, 
          data_type, 
          decode(data_type, 'NUMBER',data_precision||','||data_scale,data_length) ;