All Forums Database
Rkgowda82 5 posts Joined 09/14
08 Sep 2014
DDL's Compare of 2 tables

Hi All,
We have two databases (DEV_RETAIL and TEST_RETAIL) used for different loads in the same Teradata Box. Different people are using these environments and before promoting the stuff from RETAIL_DEV to RETAIL_TEST and to Production, i would like to make sure that DDL's are exactly matching between DEV and TEST.
Using DBC.Columns table can compare DDL of 2 tables, is there any body have a query or Stored procedure to compare table name, column name n column type of 2 different database on same Teradata box?

Priya01 10 posts Joined 08/14
09 Sep 2014

Check if this query can help you.
select (CASE when (a.cnt=(select count(*) from dbc.columns c where trim(c.databasename)='<any one database name>' and trim(c.tablename)='<table name>')) then 'Matching' else 'Not Matching' END) rslt
(select count(*) cnt
dbc.tables t1,
dbc.tables t2,
dbc.columns c1,
dbc.columns c2
where trim(t1.tablename)=trim(t2.tablename)
and trim(c1.columnname)=trim(c2.columnname)
and trim(t1.databasename)=trim(c1.databasename)
and trim(t2.databasename)=trim(c2.databasename)
and trim(t1.tablename)=trim(c1.tablename)
and trim(c1.tablename)=trim(c2.tablename)
and trim(c1.columnformat)=trim(c2.columnformat)
and trim(c1.columntype)=trim(c2.columntype)
and trim(t1.databasename) = '<database 1 name>'
and trim(t2.databasename) = '<database 2 name>'
and trim(t1.tablename) like '<table name>') a;

Glass 225 posts Joined 04/10
09 Sep 2014

There is more to consider than columns.
for example: Indexes, partitioning, compression, fallback, blocksize,etc.
If you can't visually compare (compare such as Ultra Edit will be faster),
you can copy the tables ddl from Prod to Dev to Test to ensure it is exactly the same using TD Administrator for instance.

mihirdhakan93 14 posts Joined 04/15
16 Oct 2015

How to compare DDL of Stored Procedure of  2 different Environments?
Any Idea?

VandeBergB 182 posts Joined 09/06
16 Oct 2015

You'll need a third party tool, Atanasuite or UltraEdit/UltraCompare, as previously mentioned

Some drink from the fountain of knowledge, others just gargle.

mihirdhakan93 14 posts Joined 04/15
02 Jun 2016

I have developed a Stored Procedure which will first Generate DDLS and then will Compare DDL's . For generation i have used Java Code wrapped in JAR. and Calling the Jar  in Loop. It Works.

You must sign in to leave a comment.