Check if 2 DB in Sync

I created the below script to test if tables in two db's are in sync.

/***************************************************/
/* Script to compare Tables in two databases */
/* Steps to follow : */
/* 1. Replace dp_tedw with name of first DB */
/* 2. Replace ed1_tedw with name of second DB */
/* 3. Save AS the file */
/* 3. Logon to BTEQ (.LOGON tdpid/usr,pwd) */
/* 4. Execute script (.RUN FILE = */
/* 5. Report file DBCompare.out */
/***************************************************/


.SET DEFAULTS
.SET WIDTH 254
.SET QUIET ON
.SET ECHOREQ ON

.OS IF EXIST DBCompare.out DEL DBCompare.out

.EXPORT REPORT FILE=DBCompare.out

SELECT '/******************************************/' (TITLE'');
SELECT '/* Database compared dp_tedw AND ed1_tedw */' (TITLE'');
SELECT '/* DATE : '||DATE||' TIME : '||TIME||' */' (TITLE'');
SELECT '/******************************************/' (TITLE'');

.REPEAT 2
SELECT '' (TITLE'');

/* Identify list of Objects in dp_tedw AND not in ed1_tedw */
SELECT '/******************************************/' (TITLE'');
SELECT '/* LIST OF OBJECTS IN dp_tedw AND NOT IN ed1_tedw */' (TITLE'');
SELECT '/******************************************/' (TITLE'');

SELECT tablename "dp_tedw TableName"
FROM dbc.tables
WHERE databasename = 'dp_tedw'
AND tablename NOT IN (SELECT tablename FROM dbc.tables WHERE databasename = 'ed1_tedw')
WITH COUNT(*) (TITLE '*** No. of Tables in dp_tedw AND NOT IN ed1_tedw')
ORDER BY 1
;

.REPEAT 2
SELECT '' (TITLE'');

/* Identify list of objects in ed1_tedw AND not in dp_tedw */
SELECT '/******************************************/' (TITLE'');
SELECT '/* LIST OF OBJECTS IN ed1_tedw AND NOT IN dp_tedw */' (TITLE'');
SELECT '/******************************************/' (TITLE'');

SELECT tablename "ed1_tedw TableName"
FROM dbc.tables
WHERE databasename = 'ed1_tedw'
AND tablename NOT IN (SELECT tablename tbl FROM dbc.tables WHERE databasename = 'dp_tedw')
WITH COUNT(*) (TITLE '*** No. of Tables in ed1_tedw AND NOT IN dp_tedw')
ORDER BY 1
;

.REPEAT 2
SELECT '' (TITLE'');

/* Identify the common entities */
SELECT '/******************************************/' (TITLE'');
SELECT '/** LIST OF COMMON OBJECTS IN dp_tedw AND ed1_tedw **/' (TITLE '');
SELECT '/******************************************/' (TITLE'');

SELECT tablename "Common Tables"
FROM dbc.tables
WHERE databasename = 'dp_tedw'
AND tablename IN (SELECT tablename FROM dbc.tables WHERE databasename = 'ed1_tedw')
WITH COUNT(*) (TITLE '*** No. of Common Tables')
ORDER BY 1
;


/* CREATE volatile table to hold common tables */
CREATE VOLATILE TABLE _common_tables
(
dbname CHAR(30)
,tablename CHAR(30)
)
UNIQUE PRIMARY INDEX (dbname, tablename)
ON COMMIT PRESERVE ROWS
;

/* Identify the common tables */
INSERT INTO _common_tables
SELECT databasename,tablename tbl
FROM dbc.tables
WHERE databasename = 'dp_tedw'
AND tbl IN (SELECT tablename tbl FROM dbc.tables WHERE databasename = 'ed1_tedw')
;


INSERT INTO _common_tables
SELECT databasename,tablename tbl
FROM dbc.tables
WHERE databasename = 'ed1_tedw'
AND tbl IN (SELECT tablename tbl FROM dbc.tables WHERE databasename = 'dp_tedw')
;


/* Identify the non-matching fields */
CREATE VOLATILE TABLE _fieldChk
(
DbName CHAR(30) NOT NULL
,TableName CHAR(30) NOT NULL
,FieldName CHAR(30)
,FieldType CHAR(2)
,MaxLength INTEGER
,FieldFormat CHAR(30)
,FieldDefault VARCHAR(30)
,FieldNull CHAR(1)
)
UNIQUE PRIMARY INDEX (dbname, tablename, fieldname)
ON COMMIT PRESERVE ROWS
;


INSERT INTO _fieldChk
SELECT cols.DatabaseName
,cols.TableName
,cols.ColumnName
,cols.ColumnType
,cols.ColumnLength
,cols.ColumnFormat
,cols.DefaultValue
,cols.Nullable
FROM dbc.columns cols, _common_tables tmp
WHERE cols.databasename = tmp.dbname
AND cols.tablename = tmp.tablename
;

.REPEAT 2
SELECT '' (TITLE'');


/* Matching columns but different field length */
SELECT '/**************************************************/' (TITLE'');
SELECT '/* LIST OF MATCHING FIELDS FROM THE COMMON TABLES */' (Title '');
SELECT '/* WITH DIFFERENT FIELD LENGTH */' (Title '');
SELECT '/**************************************************/' (TITLE'');

.SET SKIPLINE ON 1

.SET SUPPRESS ON 1,2

SELECT
t1.TableName
,t1.FieldName
,t1.FieldType
,der.FieldType
,min(der.MaxLength ) "Len in Fld1"
,max(t1.MaxLength ) "Len in Fld2"
FROM
_fieldChk t1
,(SELECT
max(DbName) dbname
,TableName
,FieldName
,FieldType
,MaxLength
, count(*) cnt
FROM _fieldChk
GROUP BY
TableName
,FieldName
,FieldType
,MaxLength
HAVING cnt <> 2
) der
WHERE t1.tablename = der.tablename
AND t1.dbname <> der.dbname
AND t1.FieldName = der.FieldName
AND t1.FieldType = der.FieldType
GROUP BY 1,2,3,4
ORDER BY 1,2
;

.REPEAT 2
SELECT '' (TITLE'');

.SET SKIPLINE OFF

/* Matching columns but different field constraint */
SELECT '/**************************************************/' (TITLE'');
SELECT '/* LIST OF MATCHING FIELDS FROM THE COMMON TABLES */' (Title '');
SELECT '/* WITH DIFFERENT FIELD FORMAT CONSTRAINT */' (Title '');
SELECT '/**************************************************/' (TITLE'');

.SET SKIPLINE ON 1

.SET SUPPRESS ON 1

SELECT
t1.TableName
,t1.FieldName
,t1.FieldType
,der.FieldType
,min(der.FieldFormat ) "Val in Fld1"
,max(t1.FieldFormat ) "Val in Fld2"
FROM
_fieldChk t1
,(SELECT
max(DbName) dbname
,TableName
,FieldName
,FieldType
,FieldFormat
,count(*) cnt
FROM _fieldChk
GROUP BY
TableName
,FieldName
,FieldType
,FieldFormat
HAVING cnt <> 2
) der
WHERE t1.tablename = der.tablename
AND t1.dbname <> der.dbname
AND t1.FieldName = der.FieldName
AND t1.FieldType = der.FieldType
GROUP BY 1,2,3,4
ORDER BY 1,2
;

.REPEAT 2
SELECT '' (TITLE'');

.SET SKIPLINE OFF

/* Matching columns but different field constraint */
SELECT '/**************************************************/' (TITLE'');
SELECT '/* LIST OF MATCHING FIELDS FROM THE COMMON TABLES */' (Title '');
SELECT '/* WITH DIFFERENT FIELD NULL CONSTRAINT */' (Title '');
SELECT '/**************************************************/' (TITLE'');

.SET SKIPLINE ON 1

.SET SUPPRESS ON 1

SELECT
t1.TableName
,t1.FieldName
,t1.FieldType
,der.FieldType
,min(der.FieldNull ) "Val in Fld1"
,max(t1.FieldNull ) "Val in Fld2"
FROM
_fieldChk t1
,(SELECT
max(DbName) dbname
,TableName
,FieldName
,FieldType
,FieldNull
,count(*) cnt
FROM _fieldChk
GROUP BY
TableName
,FieldName
,FieldType
,FieldNull
HAVING cnt <> 2
) der
WHERE t1.tablename = der.tablename
AND t1.dbname <> der.dbname
AND t1.FieldName = der.FieldName
AND t1.FieldType = der.FieldType
GROUP BY 1,2,3,4
ORDER BY 1,2
;


.REPEAT 2
SELECT '' (TITLE'');

.SET SKIPLINE OFF

/* Matching columns but different field constraint */
SELECT '/**************************************************/' (TITLE'');
SELECT '/* LIST OF MATCHING FIELDS FROM THE COMMON TABLES */' (Title '');
SELECT '/* WITH DIFFERENT FIELD DEFAULT CONSTRAINT */' (Title '');
SELECT '/**************************************************/' (TITLE'');

.SET SKIPLINE ON 1

.SET SUPPRESS ON 1

SELECT
t1.TableName
,t1.FieldName
,t1.FieldType
,der.FieldType
,min(COALESCE(t1.FieldDefault,'NULL')) (NAMED "Val in Fld1")
,max(COALESCE(der.FieldDefault,'NULL')) (NAMED "Val in Fld2")
FROM
_fieldChk t1
,(SELECT
max(DbName) dbname
,TableName
,FieldName
,FieldType
,FieldDefault
,count(*) cnt
FROM _fieldChk
GROUP BY
TableName
,FieldName
,FieldType
,FieldDefault
HAVING cnt <> 2
) der
WHERE t1.tablename = der.tablename
AND t1.dbname <> der.dbname
AND t1.FieldName = der.FieldName
AND t1.FieldType = der.FieldType
GROUP BY 1,2,3,4
ORDER BY 1,2
;

.REPEAT 2
SELECT '' (TITLE'');

.SET SKIPLINE OFF

/* Non matching columns */
SELECT '/******************************************************/' (TITLE'');
SELECT '/* LIST OF NON-MATCHING FIELDS FROM THE COMMON TABLES */' (Title '');
SELECT '/******************************************************/' (TITLE'');

.SET SKIPLINE ON 2

.SET SUPPRESS ON 1,2

SELECT
max(DbName) DatabaseName
,TableName
,FieldName
,FieldType
FROM _fieldChk
GROUP BY
TableName
,FieldName
,FieldType
HAVING count(*) <> 2
ORDER BY 1,2
;


.REPEAT 2
SELECT '' (TITLE'');

.SET SKIPLINE OFF

SELECT '/*******************************************************/' (TITLE'');
SELECT '/* LIST OF NON-MATCHING Indexes FROM THE COMMON TABLES */' (Title '');
SELECT '/*******************************************************/' (TITLE'');

/* Identify the non-matching PI */
CREATE VOLATILE TABLE _indexChk
(
DbName CHAR(30) NOT NULL
,TableName CHAR(30) NOT NULL
,FieldName CHAR(30)
,IndexType CHAR(1)
,UniqueFlag CHAR(1)
,FieldPosition SMALLINT
)
UNIQUE PRIMARY INDEX (dbname, tablename, fieldname, IndexType)
ON COMMIT PRESERVE ROWS
;

INSERT INTO _indexChk
SELECT
DatabaseName
,TableName
,ColumnName
,IndexType
,UniqueFlag
,ColumnPosition
FROM dbc.indices
WHERE tablename in ( SELECT DISTINCT tablename FROM _common_tables )
AND databasename in ( SELECT DISTINCT dbname FROM _common_tables )
;

.SET SKIPLINE ON 2

.SET SUPPRESS ON 1,2

SELECT
ick.DbName
,ick.TableName
,ick.FieldName
,ick.IndexType
,ick.UniqueFlag
,ick.FieldPosition
FROM _indexChk ick
JOIN (SELECT tablename, fieldname
FROM _indexChk
HAVING COUNT(*) <>2
GROUP BY 1,2) der
ON ick.tablename = der.tablename
AND ick.fieldname = der.fieldname
ORDER BY 1,2,4,6
;

.REPEAT 2
SELECT '' (TITLE'');

.SET SKIPLINE OFF

SELECT '/*******************************************************/' (TITLE'');
SELECT '/* LIST OF NON-MATCHING Sequence/Order FROM THE COMMON TABLES */' (Title '');
SELECT '/*******************************************************/' (TITLE'');

.SET SKIPLINE ON 2

.SET SUPPRESS ON 1,2

SELECT
der1.databasename
,der1.tablename
,der1.columnname
,der1.columnid "Seq in dp_tedw"
,der2.columnid "Seq in ed1_tedw"
FROM
(SELECT
databasename
,tablename
,columnname
,columnid
FROM dbc.columns
WHERE tablename in ( SELECT DISTINCT tablename FROM _common_tables )
and databasename in ( SELECT DISTINCT dbname FROM _common_tables )
) der1
,(SELECT
databasename
,tablename
,columnname
,columnid
FROM dbc.columns
WHERE tablename in ( SELECT DISTINCT tablename FROM _common_tables )
and databasename in ( SELECT DISTINCT dbname FROM _common_tables )
) der2
WHERE der1.databasename = 'dp_tedw'
and der1.tablename = der2.tablename
AND der1.columnname = der2.columnname
AND der1.columnid <> der2.columnid
ORDER BY 1,2
;

.REPEAT 2
SELECT '' (TITLE'');

.SET SKIPLINE OFF

SELECT '/*******************************************************/' (TITLE'');
SELECT '/* -------------------- End of File ------------------ */' (Title '');
SELECT '/*******************************************************/' (TITLE'');


.EXPORT RESET

.SET DEFAULTS

DROP TABLE _common_tables;

DROP TABLE _fieldChk;

DROP TABLE _indexChk;



/* End of Script */