Script to compare two databases (Tables and Views definitions)

Database
Enthusiast

Script to compare two databases (Tables and Views definitions)

All,

I have to compare two databases (Tables and Views defintions), Both the databases have the same number of tables and views, but I have to investigate the difference in there definitions, for example SET or MULTISET TABLES.

If anyone have scripts to make this comparison, Please let me know!

Khurram
Tags (1)
6 REPLIES
Enthusiast

Re: Script to compare two databases (Tables and Views definitions)

Hi Khurram,

This script is not tested but I think you will get the logic easily.  You can customize and expand more as per your requirements. Once you get the output, you can open in excel sheet or write unix script to do comparison, according to your convenience:

.export....all what you need

........EXPORT REPORT FILE = abc.sql

select case when  when tablekind = 't' then 'show table '

                 when tablekind = 'v' then 'show view '

            end || trim(databasename) || '.' || trim(tablename) || ' ;' (title '')

       from dbc.tables  tbl_view

      where  tbl_view.tablekind in('t','v')

        AND DATABASENAME IN

            (

             'db1','db2'

            )

        and trim(tablename) not like all ('al%','et%','lt%','tl%','uv')

        and tbl_view.tablename not in

            ( select trim(childtable) || '_' || trim(indexid)

                 from dbc.all_ri_children

            )

      group by 1

      order by 1;

.EXPORT RESET

     .EXPORT REPORT FILE = abc.txt

     .RUN FILE abc.sql

     .EXPORT RESET

     .LOGOFF

     .QUIT

Cheers,

Raja

Enthusiast

Re: Script to compare two databases (Tables and Views definitions)

Thank you Raja, I will try the script.

Khurram
Enthusiast

Re: Script to compare two databases (Tables and Views definitions)

You are welcome. 

Good luck,

Raja

Enthusiast

Re: Script to compare two databases (Tables and Views definitions)

If you have access on DBC then you can write SQL statement to compare text for TVM.CreateText. 

You might need to join dbc.TVM  with dbc.dbase to get appropriate result.

Enthusiast

Re: Script to compare two databases (Tables and Views definitions)

Thank you Kawish, I will try to use this, but if you paste some sample query, It would be a great help :)

Khurram
Enthusiast

Re: Script to compare two databases (Tables and Views definitions)

I did it using the below query:

SELECT DatabaseName,TableName,requesttext FROM dbc.tables

WHERE DATABASENAME = 'DATABAE_TO_COMPARE' AND TableKind = 'T' and requesttext is not null

MINUS

SELECT DatabaseName,TableName,requesttext FROM dbc.tables

WHERE DATABASENAME = 'DATABAE_TO_COMPARE_WITH' AND TableKind = 'T' and requesttext is not null

and if you want to compare at database level:

SELECT databasename from dbc.databases where ownername = 'db1_Owner_Name'

MINUS 

SELECT databasename FROM dbc.databases where where ownername = 'db2_Owner_Name'

Khurram