Archives of the TeradataForum

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

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

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: [email protected] or [email protected].

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

     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>>