DDL's Compare of 2 tables

Database
Enthusiast

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?

6 REPLIES
Enthusiast

Re: DDL's Compare of 2 tables

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

from

(select count(*) cnt

from

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;

Enthusiast

Re: DDL's Compare of 2 tables

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.

Rglass

Enthusiast

Re: DDL's Compare of 2 tables

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

Any Idea?

Enthusiast

Re: DDL's Compare of 2 tables

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

Enthusiast

Re: DDL's Compare of 2 tables

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.

Enthusiast

Re: DDL's Compare of 2 tables

HI  , 

 

I am also facing same issue. I want to compare multiple table schemas in to different servers. Can you suggest me the best approach and if possible can you share any sample script so that i can dig my self and work on it. 

 

Thanks in advance