Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Compare 2 tables in 2 different databases
 
From:   Myrna, James

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019