Comparing HANA Schemas

[asim_munshi]

Recently got tasked with a repetitive need to compare 2(n) HANA schemas.  Comparison was on the lines

1. Do the same number of tables exist in both schemas? Extensible to other database objects viz. procedures, views etc


2. Is there a difference in the row count of tables between the 2 schemas?


3. Output a list of missing tables between the schemas etc

Could not find existing functionality or anything readily available within HANA.

Including here the below sample procedure to help those looking for similar functionality. The procedure uses 2 logging tables TEST_SCHEMA_LOG and SCHEMA_TABCOUNT_LOG.

CREATE COLUMN TABLE "SCHEMA_TABCOUNT_LOG" ("TABLE_NAME" VARCHAR(500),

	 "TGTCOUNT" BIGINT CS_FIXED,

	 "SRCCOUNT" BIGINT CS_FIXED,

	 "DIFF" BIGINT CS_FIXED) UNLOAD PRIORITY 5 AUTO MERGE 



CREATE COLUMN TABLE "TEST_SCHEMA_LOG" ("SCHEMA_NAME" VARCHAR(100),

	 "PARAM" VARCHAR(100),

	 "VALUE" BIGINT CS_FIXED,

	 "RUNTIME" LONGDATE CS_LONGDATE) UNLOAD PRIORITY 5 AUTO MERGE 

The system view SYS.M_TABLES contains the information you are looking for. The below procedures leverages this view and organizes the comparisons metrics

CREATE or REPLACE PROCEDURE ZCREATE_COMPARE_SCHEMAS(IN SC1 VARCHAR(30),IN SC2 VARCHAR(30))

    LANGUAGE SQLSCRIPT

	SQL SECURITY INVOKER

	--READS SQL DATA 

	AS

BEGIN

	USING SQLSCRIPT_STRING AS lib;

DECLARE BEGIN_TIME,END_TIME TIMESTAMP;

DECLARE TABUCOUNT INTEGER;

DECLARE V_SELECT_EXTR VARCHAR(5000);

DECLARE V_SELECT_REL VARCHAR(5000);

DECLARE V_INSERT_REL VARCHAR(5000);

DECLARE i INT;

DECLARE b INT;



SELECT CURRENT_TIMESTAMP INTO END_TIME FROM DUMMY;

TRUNCATE TABLE "TEST_SCHEMA_LOG";

TRUNCATE TABLE "SCHEMA_TABCOUNT_LOG";

		V_SELECT_EXTR = 'select count(*) FROM "SYS"."M_TABLES" WHERE SCHEMA_NAME = '||''''||:SC1||'''';

		EXEC(V_SELECT_EXTR) into TABUCOUNT;

		INSERT INTO "TEST_SCHEMA_LOG"("SCHEMA_NAME","PARAM","VALUE","RUNTIME") 

       	VALUES (:SC1,'TABLECOUNT',:TABUCOUNT,:END_TIME) ; --:TABUCOUNT

       	V_SELECT_EXTR = 'select count(*) FROM "SYS"."M_TABLES" WHERE SCHEMA_NAME = '||''''||:SC2||'''';

		EXEC(V_SELECT_EXTR) into TABUCOUNT;

		INSERT INTO "TEST_SCHEMA_LOG"("SCHEMA_NAME","PARAM","VALUE","RUNTIME") 

       	VALUES (:SC2,'TABLECOUNT',:TABUCOUNT,:END_TIME) ;--:TABUCOUNT

---

		INSERT INTO "SCHEMA_TABCOUNT_LOG"

		SELECT TGT.SCHEMA_NAME||'.'||TGT.TABLE_NAME,TGT.RECORD_COUNT AS TGTRECCOUNT,SRC.RECORD_COUNT AS SRCRECCOUNT,(TGT.RECORD_COUNT - SRC.RECORD_COUNT)AS DIFF

 		FROM

		(SELECT "SCHEMA_NAME","TABLE_NAME","RECORD_COUNT"

		FROM "SYS"."M_TABLES" WHERE SCHEMA_NAME = :SC1) AS TGT --CHECK

		INNER JOIN

		(SELECT "TABLE_NAME","RECORD_COUNT"

		FROM "SYS"."M_TABLES" WHERE SCHEMA_NAME = :SC2) AS SRC --CHECK

		ON TGT.TABLE_NAME = SRC.TABLE_NAME;

END;

Execution

CALL "ZCREATE_COMPARE_SCHEMAS" ('schema1', 'schema2')

Output

schema_artefact_compare

schema_rowcount_compare

 

The procedure can be easily extended when you have a number of schemas to compare and there is a pattern you can use for comparing them using arrays. Similar extension for all the database artefact types.