Compare DDL's of tables in 2 testing phases - find differences

Database
Enthusiast

Compare DDL's of tables in 2 testing phases - find differences

Greetings Experts,

I have a database in which there are around 600 tables being used in SIT.  As we are moving to UAT, I need to compare the DDL's of the tables (column names, datatypes, size) in SIT that are different to that of in UAT.  As of now I am mulling over the following approaches. (I do not have PERM space)

FIRST APPROACH:

create volatile table SIT (s_tablename varchar(30), s_ddl varchar(30000)) on commit preserve rows;

create volatile table UAT (u_tablename varchar(30), u_ddl varchar(30000)) on commit preserve rows;

DATABASE SIT;

How can I generate the DDL of the above with a select statement to insert into the above values.

SELECT TRIM(TABLENAME), 'SHOW TABLE ' || TRIM(TABLENAME) from dbc.tables where databasename='SIT' and tablekind='T' order by tablename;

If I can have the resultant of the SHOW TABLE i.e., DDL along with table name, I could run a direct insert select into the SIT table (something like below)

insert into SIT select trim(tablename), <DDL_OF_TABLE> from dbc.tables where databasename='SIT' and tablekind='T'
and into UAT values as appropriately so that I could LEFT JOIN them as below to find out the differences.

select sit.s_tablename, uat.u_tablename from sit left join uat on sit.s_tablename = uat.u_tablename and sit.s_ddl=uat.u_ddl where uat.u_tablename is NULL;

SECOND APPROACH:

select all the column information from dbc.columns as below from both sit and uat and compare them in excel sheet.

SEL TABLENAME, COLUMNNAME, COLUMNFORMAT, COLUMNTYYPE, COLUMNLENGTH, NULLABLE,
DECIMALTOTALDIGITS, DECIMALFRACTIONALDIGITS FROM DBC.COLUMNS WHERE DATABASENAME='SIT'
ORDER BY TABLENAME, COLUMNNAME

2a)--

As the resultant is huge (187000 rows -- for around 600 tables), the excel sheet is taking very long to compare and give the resultant.  I could break down the above query into chunks and compare it.  However, I am trying to achieve this in one part.

2b)--

One more option is to import the above select resultant into the volatile tables through sql assistant (I do not have access to run utilities) and do a minus operation as below.

sel * from SIT minus sel * from UAT;

However, I feel that it is a time consuming process of loading around 187000 rows into 2 volatile tables through SQL assistant import.  Also, I should overcome the null handling using zeroifnull or some other function during the above select over dbc.columns to avoid any further issues if so.

Can you please elucidate on the pros and cons of both the approaches or any more feasible solution.  Awaiting respones, thanks a ton!

Tags (2)
1 REPLY
Enthusiast

Re: Compare DDL's of tables in 2 testing phases - find differences

I think all of the information you need is located in dbc tables. Just skip the excel spreadsheet, the results will return descrepencies

and TD won't blink at 187000 rows.

Sel * from dbc.columns where databasename = 'SIT_dbs'

minus

Sel * from dbc.columns where databasename = 'UAT_dbs;

Sel * from dbc.indices where databasename = 'SIT_dbs'

minus

Sel * from dbc.indices where databasename = 'UAT_dbs';

indexconstraints, etc.

rglass