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?
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
and trim(t1.databasename) = '<database 1 name>'
and trim(t2.databasename) = '<database 2 name>'
and trim(t1.tablename) like '<table name>') a;
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.
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.
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