Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 18 Dec 2004 @ 07:13:15 GMT


     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 


Subj:   Useful Data Dictionary Queries - Mismatched Data Types
 
From:   Terry Stover

If your data warehouse is loading from multiple legacy systems, or when different subject areas are added over time you frequently find that data types were not applied consistently. Implicit type conversions can be a major performance hit and should be avoided. This query identifies the columns that may have an issue. The output shows all the instances of a given column name, when any one instance has a different data type.

     /* ############  FIND MISMATCHED COLUMN TYPES  ############
     Mismatched column types having same column name in different table with
     different data type or length.  (if it's not the same attribute then it
     should have a different name).

     If different data type columns are joined then one has to be converted,
     causing performance hit.

     Mismatched types may cause optimizer to not use PI's and NUSI's

     Also, it's been my observation that implicit type conversion isn't
     necessarily consistent across releases.

     Author:  Terry Stover, Levi Strauss & Co.  2003-11-19.
              Comments & feedback are welcome: tstover@levi.com or terry.stover@comcast.net.

     ########################################################### */

     SELECT distinct a.columnname, a.columntype, a.columnlength, a.tablename
     FROM dbc.columns a, dbc.columns b
     WHERE a.databasename = b.databasename
       AND a.tablename <> b.tablename
       AND a.columnname = b.columnname
       AND (a.columntype <> b.columntype OR a.columnlength <> b.columnlength)
       AND a.databasename in ('dw_data')
     ORDER BY 1,2,3,4


     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016