Archives of the TeradataForum

Message Posted: Thu, 17 Jan 2013 @ 14:01:02 GMT

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

Roopalini,

I started working on a SQL that does a table structure compare (shown below). Perhaps you can start with this and modify it to suit your needs.

     SELECT dbc.DatabaseName,dbc.TableName, dbc.ColumnName, dbc.ColumnType, dbc.ColumnLength,
     dbc.DecimalTotalDigits, dbc.DecimalFractionalDigits,
            pdc.DatabaseName,pdc.TableName, pdc.ColumnName, pdc.ColumnType, pdc.ColumnLength,
     pdc.DecimalTotalDigits, pdc.DecimalFractionalDigits

     FROM

     (SELECT DatabaseName,TableName, ColumnName, ColumnType,
       ColumnLength, DecimalTotalDigits, DecimalFractionalDigits, CharType, UpperCaseFlag,
     Nullable,
       ROW_NUMBER() OVER (PARTITION BY tablename ORDER BY Columnid) AS ColumnId
       FROM dbc.COLUMNS
      WHERE databasename = 'Dev_ABC'
     ) dbc

     FULL OUTER JOIN

     (SELECT DatabaseName,TableName, ColumnName, ColumnType,
       ColumnLength, DecimalTotalDigits, DecimalFractionalDigits, CharType, UpperCaseFlag,
     Nullable,
       ROW_NUMBER() OVER (PARTITION BY tablename ORDER BY Columnid) AS ColumnId FROM
     dbc.COLUMNS WHERE databasename = 'Prd_ABC'
       ) pdc


       ON dbc.tablename = pdc.tablename
       AND dbc.ColumnId = pdc.ColumnId
     --AND dbc.Columnname = pdc.Columnname  --< use either column name or column position to
     join, based on what you are looking for WHERE dbc.ColumnLength <> pdc.ColumnLength
        OR dbc.DecimalTotalDigits <> pdc.DecimalTotalDigits
        OR dbc.CharType <> pdc.CharType
        OR dbc.UpperCaseFlag <> pdc.UpperCaseFlag
        OR dbc.Nullable <> pdc.Nullable
        OR dbc.Tablename IS NULL
        OR pdc.Tablename IS NULL
     ORDER BY COALESCE(dbc.Tablename, pdc.Tablename), COALESCE(dbc.ColumnId, pdc.ColumnId) ;

Regards,

Jim

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