USE tables_backup;

DROP PROCEDURE IF EXISTS SP_DBSCHEMACOMPARE_WITHIN_CLUSTER;

DELIMITER //

CREATE PROCEDURE SP_DBSCHEMACOMPARE_WITHIN_CLUSTER (SOURCEDBNAME VARCHAR(64),

DESTINATIONDBNAME VARCHAR(64))

BEGIN

SET @SOURCEDBNAME = SOURCEDBNAME COLLATE utf8_tolower_ci, @DESTINATIONDBNAME =

DESTINATIONDBNAME COLLATE utf8_tolower_ci;

DROP TABLE IF EXISTS DB_COMPARE;

CREATE TABLE IF NOT EXISTS DB_COMPARE

DATABASE_NAME VARCHAR(64) COLLATE utf8_tolower_ci

,TABLE_NAME VARCHAR(64) COLLATE utf8_tolower_ci

,OBJECT_NAME VARCHAR(64) COLLATE utf8_tolower_ci

,OBJECT_TYPE VARCHAR(64) COLLATE utf8_tolower_ci

,MESSAGE VARCHAR(255) COLLATE utf8_tolower_ci

,ISMISSING BIT

,INDEX_COLS VARCHAR(2000) COLLATE utf8_tolower_ci

,INDEX_TYPE VARCHAR(100) COLLATE utf8_tolower_ci

,KEY IX_TYPE (OBJECT_TYPE)

,KEY IX_NAME (DATABASE_NAME, TABLE_NAME)

);

-- Gets the tables exist only in source

INSERT INTO DB_COMPARE

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, NULL, 'Table', CONCAT('Table exists only in

source database `', S.TABLE_SCHEMA, '`.`', S.TABLE_NAME, '`'), 1, NULL, NULL

FROM INFORMATION_SCHEMA.TABLES S
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES D ON D.TABLE_SCHEMA = @DESTINATIONDBNAME
AND S.TABLE_NAME = D.TABLE_NAME

WHERE S.TABLE_SCHEMA = @SOURCEDBNAME

AND D.TABLE_NAME IS NULL;

-- Gets the table exist only in destination

INSERT INTO DB_COMPARE

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, NULL, 'Table', CONCAT('Table exists only in

destination database `', S.TABLE_SCHEMA, '`.`', S.TABLE_NAME, '`'), 1, NULL, NULL

FROM INFORMATION_SCHEMA.TABLES S

LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES D ON D.TABLE_SCHEMA = @SOURCEDBNAME AND

S.TABLE_NAME = D.TABLE_NAME

WHERE S.TABLE_SCHEMA = @DESTINATIONDBNAME

AND D.TABLE_NAME IS NULL;

-- Gets the columns, which are not part of any missing table, exist in source

INSERT INTO DB_COMPARE

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.COLUMN_NAME, 'Column', CONCAT('Column exists

only in source database `', S.TABLE_SCHEMA, '`.`', S.TABLE_NAME, '`'), 1, NULL,
NULL

FROM INFORMATION_SCHEMA.COLUMNS S

INNER JOIN INFORMATION_SCHEMA.TABLES T ON S.TABLE_NAME = T.TABLE_NAME AND

T.TABLE_SCHEMA = @DESTINATIONDBNAME

LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS D ON D.TABLE_SCHEMA = @DESTINATIONDBNAME

AND S.TABLE_NAME = D.TABLE_NAME AND S.COLUMN_NAME = D.COLUMN_NAME

WHERE S.TABLE_SCHEMA = @SOURCEDBNAME

AND D.COLUMN_NAME IS NULL;

-- Gets the columns, which are not part of any missing table, exist in destination

INSERT INTO DB_COMPARE

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.COLUMN_NAME, 'Column', CONCAT('Column exists

only in destination database `', S.TABLE_SCHEMA, '`.`', S.TABLE_NAME, '`'), 1,
NULL, NULL

FROM INFORMATION_SCHEMA.COLUMNS S
INNER JOIN INFORMATION_SCHEMA.TABLES T ON S.TABLE_NAME = T.TABLE_NAME AND
T.TABLE_SCHEMA = @SOURCEDBNAME

LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS D ON D.TABLE_SCHEMA = @SOURCEDBNAME AND

S.TABLE_NAME = D.TABLE_NAME AND S.COLUMN_NAME = D.COLUMN_NAME

WHERE S.TABLE_SCHEMA = @DESTINATIONDBNAME

AND D.COLUMN_NAME IS NULL;

-- Get entries for change in column

-- The OR conditions shall be replaced by SHA2 HASHING

INSERT INTO DB_COMPARE

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.COLUMN_NAME, 'Column', CONCAT('Column

property is changed `', S.TABLE_SCHEMA, '`.`', S.TABLE_NAME, '`'), 0, NULL, NULL

FROM INFORMATION_SCHEMA.COLUMNS S

INNER JOIN INFORMATION_SCHEMA.COLUMNS D ON D.TABLE_SCHEMA = @DESTINATIONDBNAME AND

S.TABLE_NAME = D.TABLE_NAME AND S.COLUMN_NAME = D.COLUMN_NAME

WHERE S.TABLE_SCHEMA = @SOURCEDBNAME

AND

COALESCE(S.COLUMN_DEFAULT COLLATE utf8_unicode_ci , '') <>

COALESCE(D.COLUMN_DEFAULT COLLATE utf8_unicode_ci , '')

OR COALESCE(S.IS_NULLABLE COLLATE utf8_unicode_ci , '') <>

COALESCE(D.IS_NULLABLE COLLATE utf8_unicode_ci , '')

/*OR COALESCE(S.DATA_TYPE, '') <> COALESCE(D.DATA_TYPE, '')

OR COALESCE(S.CHARACTER_MAXIMUM_LENGTH, 0) <>
COALESCE(D.CHARACTER_MAXIMUM_LENGTH, 0)

OR COALESCE(S.NUMERIC_PRECISION, 0) <>
COALESCE(D.NUMERIC_PRECISION, 0)

OR COALESCE(S.NUMERIC_SCALE, 0) <> COALESCE(D.NUMERIC_SCALE, 0)

OR COALESCE(S.COLUMN_TYPE, '') <> COALESCE(D.COLUMN_TYPE,'')

OR COALESCE(S.DATETIME_PRECISION,0) <>
COALESCE(D.DATETIME_PRECISION,0)*/

OR COALESCE(S.CHARACTER_SET_NAME COLLATE utf8_unicode_ci ,'') <>

COALESCE(D.CHARACTER_SET_NAME COLLATE utf8_unicode_ci ,'')

OR COALESCE(S.COLUMN_TYPE COLLATE utf8_unicode_ci, '') <>

COALESCE(D.COLUMN_TYPE COLLATE utf8_unicode_ci,'')
 OR COALESCE([Link] COLLATE utf8_unicode_ci ,'') <>
COALESCE([Link] COLLATE utf8_unicode_ci,'')

OR COALESCE(S.COLUMN_COMMENT COLLATE utf8_unicode_ci ,'') <>

COALESCE(D.COLUMN_COMMENT COLLATE utf8_unicode_ci ,'')

OR COALESCE(S.GENERATION_EXPRESSION COLLATE utf8_unicode_ci,'') <>

COALESCE(D.GENERATION_EXPRESSION COLLATE utf8_unicode_ci ,'')

);

-- AND SHA2(CONCAT(S.COLUMN_DEFAULT, description), 256) =

SHA2(CONCAT(name, description), 256);

-- Gets the Indexes, which are not associated with constraints, exist only in
destination

INSERT INTO DB_COMPARE

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME, 'Index', CONCAT('Index exists

only in destination database `', S.TABLE_SCHEMA, '`.`', S.TABLE_NAME, '`'), 1,
GROUP_CONCAT( "`", COALESCE(S.COLUMN_NAME COLLATE utf8_unicode_ci ,[Link]
COLLATE utf8_unicode_ci ), "`", CASE WHEN [Link] COLLATE utf8_unicode_ci =
'D' THEN ' DESC ' ELSE '' END, COALESCE(CONCAT(' COMMENT', NULLIF(TRIM([Link]
COLLATE utf8_unicode_ci),'') ), '') , CASE WHEN S.IS_VISIBLE COLLATE
utf8_unicode_ci = 'NO' THEN ' INVISIBLE ' ELSE '' END ORDER BY S.SEQ_IN_INDEX ASC
SEPARATOR "," ) INDEX_COLS, S.INDEX_TYPE

FROM INFORMATION_SCHEMA.STATISTICS S

INNER JOIN INFORMATION_SCHEMA.TABLES T ON S.TABLE_NAME = T.TABLE_NAME AND

T.TABLE_SCHEMA = @SOURCEDBNAME

LEFT OUTER JOIN INFORMATION_SCHEMA.STATISTICS D ON D.TABLE_SCHEMA = @SOURCEDBNAME

AND S.TABLE_NAME = D.TABLE_NAME AND S.INDEX_NAME = D.INDEX_NAME

WHERE S.TABLE_SCHEMA = @DESTINATIONDBNAME

-- AND S.NON_UNIQUE = 1

AND D.INDEX_NAME IS NULL

GROUP BY S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME, S.INDEX_TYPE;

-- Gets the Indexes, which are not associated with constraints, exist only in
source

INSERT INTO DB_COMPARE

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME, 'Index', CONCAT('Index exists

only in source database `', S.TABLE_SCHEMA, '`.`', S.TABLE_NAME, '`'), 1,
GROUP_CONCAT( "`", COALESCE(S.COLUMN_NAME COLLATE utf8_unicode_ci ,[Link]
COLLATE utf8_unicode_ci ), "`", CASE WHEN [Link] COLLATE utf8_unicode_ci =
'D' THEN ' DESC ' ELSE '' END, COALESCE(CONCAT(' COMMENT', NULLIF(TRIM([Link]
COLLATE utf8_unicode_ci),'') ), '') , CASE WHEN S.IS_VISIBLE COLLATE
utf8_unicode_ci = 'NO' THEN ' INVISIBLE ' ELSE '' END ORDER BY S.SEQ_IN_INDEX ASC
SEPARATOR "," ) INDEX_COLS, S.INDEX_TYPE

FROM INFORMATION_SCHEMA.STATISTICS S

INNER JOIN INFORMATION_SCHEMA.TABLES T ON S.TABLE_NAME = T.TABLE_NAME AND

T.TABLE_SCHEMA = @DESTINATIONDBNAME

LEFT OUTER JOIN INFORMATION_SCHEMA.STATISTICS D ON D.TABLE_SCHEMA =

@DESTINATIONDBNAME AND S.TABLE_NAME = D.TABLE_NAME AND S.INDEX_NAME = D.INDEX_NAME

WHERE S.TABLE_SCHEMA = @SOURCEDBNAME

-- AND S.NON_UNIQUE = 1

AND D.INDEX_NAME IS NULL

GROUP BY S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME, S.INDEX_TYPE;

-- Gets the Indexes, which are not associated with constraints, exist in both
databases but with difference in its properties

INSERT INTO DB_COMPARE

SELECT S.*

FROM (

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME, 'Index',

CONCAT('Index with change in properties `', S.TABLE_SCHEMA, '`.`', S.TABLE_NAME,
'`'), 0

, GROUP_CONCAT( "`", COALESCE(S.COLUMN_NAME COLLATE

utf8_unicode_ci ,[Link] COLLATE utf8_unicode_ci ), "`", CASE WHEN [Link]
COLLATE utf8_unicode_ci = 'D' THEN ' DESC ' ELSE '' END, COALESCE(CONCAT('
COMMENT', NULLIF(TRIM([Link] COLLATE utf8_unicode_ci),'') ), '') , CASE WHEN
S.IS_VISIBLE COLLATE utf8_unicode_ci = 'NO' THEN ' INVISIBLE ' ELSE '' END ORDER
BY S.SEQ_IN_INDEX ASC SEPARATOR "," ) INDEX_COLS, S.INDEX_TYPE

FROM INFORMATION_SCHEMA.STATISTICS S

WHERE S.TABLE_SCHEMA = @SOURCEDBNAME

GROUP BY S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME, S.INDEX_TYPE

)S

INNER JOIN

(
 SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME

,GROUP_CONCAT( "`", COALESCE(S.COLUMN_NAME COLLATE

utf8_unicode_ci ,[Link] COLLATE utf8_unicode_ci ), "`", CASE WHEN [Link]
COLLATE utf8_unicode_ci = 'D' THEN ' DESC ' ELSE '' END, COALESCE(CONCAT('
COMMENT', NULLIF(TRIM([Link] COLLATE utf8_unicode_ci),'') ), '') , CASE WHEN
S.IS_VISIBLE COLLATE utf8_unicode_ci = 'NO' THEN ' INVISIBLE ' ELSE '' END ORDER
BY S.SEQ_IN_INDEX ASC SEPARATOR "," ) INDEX_COLS

FROM INFORMATION_SCHEMA.STATISTICS S

WHERE S.TABLE_SCHEMA = @DESTINATIONDBNAME

GROUP BY S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME

) D ON S.TABLE_NAME = D.TABLE_NAME

AND S.INDEX_NAME = D.INDEX_NAME

AND S.INDEX_COLS <> D.INDEX_COLS;

-- Gets missing check constraint from source database

INSERT INTO DB_COMPARE (DATABASE_NAME, TABLE_NAME, OBJECT_NAME, OBJECT_TYPE,

MESSAGE, ISMISSING)

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.CONSTRAINT_NAME, S.CONSTRAINT_TYPE, CONCAT('

Missing ', S.CONSTRAINT_TYPE, '( ', S.CONSTRAINT_NAME, ') in source database'), 1

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS S

INNER JOIN INFORMATION_SCHEMA.TABLES T ON S.TABLE_NAME = T.TABLE_NAME AND

T.TABLE_SCHEMA = @DESTINATIONDBNAME

LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS D ON D.TABLE_SCHEMA =

@DESTINATIONDBNAME AND S.TABLE_NAME = D.TABLE_NAME AND S.CONSTRAINT_NAME =
D.CONSTRAINT_NAME AND S.CONSTRAINT_TYPE = D.CONSTRAINT_TYPE

WHERE S.TABLE_SCHEMA = @SOURCEDBNAME

AND D.CONSTRAINT_NAME IS NULL;

-- Gets missing check constraint from destination database

INSERT INTO DB_COMPARE (DATABASE_NAME, TABLE_NAME, OBJECT_NAME, OBJECT_TYPE,

MESSAGE, ISMISSING)

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.CONSTRAINT_NAME, S.CONSTRAINT_TYPE, CONCAT('

Missing ', S.CONSTRAINT_TYPE, '( ', S.CONSTRAINT_NAME, ') in destination
database'), 1

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS S

INNER JOIN INFORMATION_SCHEMA.TABLES T ON S.TABLE_NAME = T.TABLE_NAME AND

T.TABLE_SCHEMA = @SOURCEDBNAME
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS D ON D.TABLE_SCHEMA =
@SOURCEDBNAME AND S.TABLE_NAME = D.TABLE_NAME AND S.CONSTRAINT_NAME =
D.CONSTRAINT_NAME AND S.CONSTRAINT_TYPE = D.CONSTRAINT_TYPE

WHERE S.TABLE_SCHEMA = @DESTINATIONDBNAME

AND D.CONSTRAINT_NAME IS NULL;

INSERT INTO DB_COMPARE

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME, 'Table', 'Missing Table

Indexes', 1, GROUP_CONCAT( "`", COALESCE(S.COLUMN_NAME COLLATE
utf8_unicode_ci ,[Link] COLLATE utf8_unicode_ci ), "`", CASE WHEN [Link]
COLLATE utf8_unicode_ci = 'D' THEN ' DESC ' ELSE '' END, COALESCE(CONCAT('
COMMENT', NULLIF(TRIM([Link] COLLATE utf8_unicode_ci),'') ), '') , CASE WHEN
S.IS_VISIBLE COLLATE utf8_unicode_ci = 'NO' THEN ' INVISIBLE ' ELSE '' END ORDER
BY S.SEQ_IN_INDEX ASC SEPARATOR "," ) INDEX_COLS, S.INDEX_TYPE

FROM INFORMATION_SCHEMA.STATISTICS S

INNER JOIN DB_COMPARE D ON S.TABLE_SCHEMA = D.DATABASE_NAME AND S.TABLE_NAME =

D.TABLE_NAME

WHERE D.OBJECT_TYPE = 'Table'

GROUP BY S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME, INDEX_TYPE;

-- Generate Table Schema for the tables exist only in source

SELECT A.*

FROM (

SELECT

CONCAT(' CREATE TABLE IF NOT EXISTS `',

@DESTINATIONDBNAME , '`.`',S.TABLE_NAME, '` (', GROUP_CONCAT( '`', COLUMN_NAME, '`
', COLUMN_TYPE

,CASE WHEN COLLATION_NAME IS NOT NULL

THEN CONCAT(' COLLATE ', COLLATION_NAME , ' ') ELSE ' ' END

,CASE WHEN GENERATION_EXPRESSION IS NULL

OR LENGTH(TRIM(GENERATION_EXPRESSION)) = 0 THEN
 CONCAT( CASE WHEN IS_NULLABLE
COLLATE utf8_unicode_ci = 'YES' THEN CONCAT(' NULL', ' DEFAULT ',
COALESCE(COLUMN_DEFAULT, 'NULL')) ELSE CONCAT(' NOT NULL', COALESCE(CONCAT('
DEFAULT ', CASE WHEN TRIM(COLUMN_DEFAULT) = '' THEN "''" ELSE CASE WHEN
SUBSTRING(TRIM(COLUMN_DEFAULT),1,1) = '(' THEN COLUMN_DEFAULT ELSE
CONCAT("'",COLUMN_DEFAULT,"'") END END), '') )END, ' ',
UPPER(LTRIM(COALESCE(REPLACE(EXTRA, 'DEFAULT_GENERATED', ''), ''))) )

ELSE

CONCAT(' GENERATED ALWAYS AS (',

GENERATION_EXPRESSION, ') ', CASE EXTRA COLLATE utf8_unicode_ci WHEN 'STORED
GENERATED' THEN ' STORED ' WHEN 'VIRTUAL GENERATED' THEN ' VIRTUAL ' ELSE '' END)

END

ORDER BY C.ORDINAL_POSITION, '

' SEPARATOR ","), COALESCE(CONCAT(', ',

MAX(I.INDEX_DEF)),''), COALESCE(CONCAT(', ', MAX(CHK.CHECK_DEF)), ''),
COALESCE(CONCAT(', ', MAX(FKEY.FKEY_DEF)), '') ,') ENGINE=InnoDB DEFAULT
CHARSET=utf8 COLLATE=utf8_unicode_ci;

') TABLE_DEFINITION

FROM DB_COMPARE S

INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON S.DATABASE_NAME =

C.TABLE_SCHEMA AND S.TABLE_NAME = C.TABLE_NAME AND S.OBJECT_NAME IS NULL

LEFT OUTER JOIN

SELECT I.DATABASE_NAME TABLE_SCHEMA

,I.TABLE_NAME

,GROUP_CONCAT(CASE
CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN 'PRIMARY' WHEN 'UNIQUE' THEN 'UNIQUE' ELSE
'' END, CASE WHEN INDEX_TYPE = 'BTREE' THEN ' KEY' ELSE CONCAT(' ', INDEX_TYPE, '
KEY ') END, '`', OBJECT_NAME, '` (', INDEX_COLS, ' )' SEPARATOR "," ) INDEX_DEF

FROM DB_COMPARE I

LEFT JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA = I.DATABASE_NAME AND
TC.TABLE_NAME = I.TABLE_NAME AND TC.CONSTRAINT_NAME = I.OBJECT_NAME

AND CONSTRAINT_TYPE NOT

IN ('CHECK','FOREIGN KEY')

WHERE I.OBJECT_TYPE = 'Table' AND

I.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 1 AND I.OBJECT_NAME IS NOT NULL

GROUP BY I.DATABASE_NAME, I.TABLE_NAME

)I ON I.TABLE_SCHEMA = S.DATABASE_NAME AND

I.TABLE_NAME = S.TABLE_NAME

LEFT OUTER JOIN

SELECT TABLE_SCHEMA

,TABLE_NAME

,GROUP_CONCAT(CHECK_DEF
SEPARATOR ",") CHECK_DEF

FROM (

SELECT TC.TABLE_SCHEMA

,TC.TABLE_NAME

,GROUP_CONCAT('
CONSTRAINT `', TC.CONSTRAINT_NAME, '` CHECK (', CHK.CHECK_CLAUSE, ')' SEPARATOR
"," ) CHECK_DEF

FROM DB_COMPARE I

INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA = I.DATABASE_NAME AND
TC.TABLE_NAME = I.TABLE_NAME AND CONSTRAINT_TYPE = 'CHECK'

INNER JOIN
INFORMATION_SCHEMA.CHECK_CONSTRAINTS CHK ON TC.TABLE_SCHEMA = CHK.CONSTRAINT_SCHEMA
AND TC.CONSTRAINT_NAME = CHK.CONSTRAINT_NAME

WHERE OBJECT_TYPE = 'Table'

AND I.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 1 AND I.OBJECT_NAME IS NULL

GROUP BY TC.TABLE_SCHEMA,
TC.TABLE_NAME

)CHK

GROUP BY TABLE_SCHEMA ,TABLE_NAME

)CHK ON CHK.TABLE_SCHEMA = S.DATABASE_NAME AND

CHK.TABLE_NAME = S.TABLE_NAME

LEFT OUTER JOIN

SELECT TABLE_SCHEMA

,TABLE_NAME

,GROUP_CONCAT(FKEY_DEF
SEPARATOR "," ) FKEY_DEF
 FROM (

SELECT TC.TABLE_SCHEMA

,TC.TABLE_NAME

,CONCAT('
CONSTRAINT `', FKEY.CONSTRAINT_NAME, '` FOREIGN KEY (', GROUP_CONCAT( '`',
FKEY.COLUMN_NAME , '`' ORDER BY FKEY.ORDINAL_POSITION SEPARATOR ","), ') REFERENCES
', '`', FKEY.REFERENCED_TABLE_NAME, '`(', GROUP_CONCAT( '`',
FKEY.REFERENCED_COLUMN_NAME , '`' ORDER BY FKEY.ORDINAL_POSITION SEPARATOR ",") ,
')'

, CASE
WHEN MAX(RC.UPDATE_RULE) = 'NO ACTION' THEN '' ELSE COALESCE(CONCAT(' ON UPDATE ',
MAX(RC.UPDATE_RULE)),'') END

, CASE
WHEN MAX(RC.DELETE_RULE) = 'NO ACTION' THEN '' ELSE COALESCE(CONCAT(' ON DELETE ',
MAX(RC.DELETE_RULE)),'') END ) FKEY_DEF

FROM DB_COMPARE I

INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA = I.DATABASE_NAME AND
TC.TABLE_NAME = I.TABLE_NAME AND CONSTRAINT_TYPE = 'FOREIGN KEY'

INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE FKEY ON TC.TABLE_SCHEMA =
FKEY.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = FKEY.CONSTRAINT_NAME

LEFT OUTER JOIN

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.CONSTRAINT_SCHEMA =
TC.TABLE_SCHEMA AND RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME

WHERE OBJECT_TYPE = 'Table'

AND I.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 1 AND I.OBJECT_NAME IS NULL

GROUP BY TC.TABLE_SCHEMA,
TC.TABLE_NAME, FKEY.CONSTRAINT_NAME, FKEY.REFERENCED_TABLE_NAME

) FKEY

GROUP BY TABLE_SCHEMA, TABLE_NAME

)FKEY ON FKEY.TABLE_SCHEMA = S.DATABASE_NAME

AND FKEY.TABLE_NAME = S.TABLE_NAME

WHERE OBJECT_TYPE = 'Table' AND S.DATABASE_NAME = @SOURCEDBNAME AND

[Link] = 1

GROUP BY S.DATABASE_NAME, S.TABLE_NAME

) A
UNION ALL

-- select tables that exists only in destination

SELECT CONCAT(' DROP TABLE IF EXISTS `', @DESTINATIONDBNAME , '`.`', S.TABLE_NAME,

'`;

')

FROM DB_COMPARE S

WHERE OBJECT_TYPE = 'Table' AND S.DATABASE_NAME = @DESTINATIONDBNAME AND

[Link] = 1

UNION ALL

-- Generate script for missing columns, index and other constraints in Source

SELECT

CONCAT(' ALTER TABLE `', @DESTINATIONDBNAME , '`.`',S.TABLE_NAME, '` ',

SUBSTRING(CONCAT(COALESCE(CONCAT(",",MAX(C.COL_DEF)), ''), COALESCE(CONCAT(', ',
MAX(I.INDEX_DEF)),''), COALESCE(CONCAT(', ', MAX(CHK.CHECK_DEF)), ''),
COALESCE(CONCAT(', ', MAX(FKEY.FKEY_DEF)), '')),2) ,';

') TABLE_DEFINITION

FROM (SELECT DISTINCT S.DATABASE_NAME, S.TABLE_NAME FROM DB_COMPARE S WHERE

S.OBJECT_TYPE <> 'Table' AND S.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 1)
S

LEFT OUTER JOIN (

SELECT GROUP_CONCAT( ' ADD COLUMN `', COLUMN_NAME, '`

', COLUMN_TYPE

,CASE WHEN COLLATION_NAME IS NOT NULL

THEN CONCAT(' COLLATE ', COLLATION_NAME , ' ') ELSE ' ' END

,CASE WHEN GENERATION_EXPRESSION IS NULL

OR LENGTH(TRIM(GENERATION_EXPRESSION)) = 0 THEN

CONCAT( CASE WHEN IS_NULLABLE

COLLATE utf8_unicode_ci = 'YES' THEN CONCAT(' NULL', ' DEFAULT ',
COALESCE(COLUMN_DEFAULT, 'NULL')) ELSE CONCAT(' NOT NULL', COALESCE(CONCAT('
DEFAULT ', CASE WHEN TRIM(COLUMN_DEFAULT) = '' THEN "''" ELSE CASE WHEN
SUBSTRING(TRIM(COLUMN_DEFAULT),1,1) = '(' THEN COLUMN_DEFAULT ELSE
CONCAT("'",COLUMN_DEFAULT,"'") END END), '') )END, ' ',
UPPER(LTRIM(COALESCE(REPLACE(EXTRA, 'DEFAULT_GENERATED', ''), ''))) )

ELSE
 CONCAT(' GENERATED ALWAYS AS (',
GENERATION_EXPRESSION, ') ', CASE EXTRA COLLATE utf8_unicode_ci WHEN 'STORED
GENERATED' THEN ' STORED ' WHEN 'VIRTUAL GENERATED' THEN ' VIRTUAL ' ELSE '' END)

END

ORDER BY C.ORDINAL_POSITION, '

' SEPARATOR ",") COL_DEF

,S.DATABASE_NAME

,S.TABLE_NAME

FROM DB_COMPARE S

LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON

S.DATABASE_NAME = C.TABLE_SCHEMA AND S.TABLE_NAME = C.TABLE_NAME AND S.OBJECT_NAME
= C.COLUMN_NAME

WHERE S.OBJECT_TYPE = 'Column' AND [Link] = 1 AND

S.DATABASE_NAME = @SOURCEDBNAME

GROUP BY S.DATABASE_NAME, S.TABLE_NAME

) C ON C.DATABASE_NAME = S.DATABASE_NAME AND C.TABLE_NAME =

S.TABLE_NAME

LEFT OUTER JOIN

SELECT I.DATABASE_NAME TABLE_SCHEMA

,I.TABLE_NAME

,GROUP_CONCAT(' ADD ', CASE CONSTRAINT_TYPE WHEN

'PRIMARY KEY' THEN 'PRIMARY' WHEN 'UNIQUE' THEN 'UNIQUE' ELSE '' END, CASE WHEN
INDEX_TYPE = 'BTREE' THEN ' KEY' ELSE CONCAT(' ', INDEX_TYPE, ' KEY ') END, '`',
OBJECT_NAME, '` (', INDEX_COLS, ' )' SEPARATOR "," ) INDEX_DEF

FROM DB_COMPARE I

LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON

TC.TABLE_SCHEMA = I.DATABASE_NAME AND TC.TABLE_NAME = I.TABLE_NAME AND
TC.CONSTRAINT_NAME = I.OBJECT_NAME AND CONSTRAINT_TYPE NOT IN ('CHECK','FOREIGN
KEY')

WHERE I.OBJECT_TYPE = 'Index' AND [Link] = 1 AND

I.DATABASE_NAME = @SOURCEDBNAME

GROUP BY I.DATABASE_NAME, I.TABLE_NAME

)I ON I.TABLE_SCHEMA = S.DATABASE_NAME AND I.TABLE_NAME =

S.TABLE_NAME

LEFT OUTER JOIN

 (

SELECT TABLE_SCHEMA

,TABLE_NAME

,GROUP_CONCAT(CHECK_DEF SEPARATOR ",") CHECK_DEF

FROM (

SELECT TC.TABLE_SCHEMA

,TC.TABLE_NAME

,GROUP_CONCAT(' ADD
CONSTRAINT `', TC.CONSTRAINT_NAME, '` CHECK (', CHK.CHECK_CLAUSE, ')' SEPARATOR
"," ) CHECK_DEF

FROM DB_COMPARE I

INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA = I.DATABASE_NAME AND
TC.TABLE_NAME = I.TABLE_NAME AND CONSTRAINT_TYPE = 'CHECK'

INNER JOIN
INFORMATION_SCHEMA.CHECK_CONSTRAINTS CHK ON TC.TABLE_SCHEMA = CHK.CONSTRAINT_SCHEMA
AND TC.CONSTRAINT_NAME = CHK.CONSTRAINT_NAME

WHERE OBJECT_TYPE = 'CHECK' AND

I.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 1

GROUP BY TC.TABLE_SCHEMA, TC.TABLE_NAME

)CHK

GROUP BY TABLE_SCHEMA, TABLE_NAME

)CHK ON CHK.TABLE_SCHEMA = S.DATABASE_NAME AND

CHK.TABLE_NAME = S.TABLE_NAME

LEFT OUTER JOIN

SELECT TABLE_SCHEMA

,TABLE_NAME

,GROUP_CONCAT(FKEY_DEF SEPARATOR "," ) FKEY_DEF

FROM (

SELECT TC.TABLE_SCHEMA

,TC.TABLE_NAME

,CONCAT(' ADD CONSTRAINT `',

FKEY.CONSTRAINT_NAME, '` FOREIGN KEY (', GROUP_CONCAT( '`', FKEY.COLUMN_NAME , '`'
ORDER BY FKEY.ORDINAL_POSITION SEPARATOR ","), ') REFERENCES ', '`',
FKEY.REFERENCED_TABLE_NAME, '`(', GROUP_CONCAT( '`', FKEY.REFERENCED_COLUMN_NAME ,
'`' ORDER BY FKEY.ORDINAL_POSITION SEPARATOR ",") , ')'

, CASE WHEN
MAX(RC.UPDATE_RULE) = 'NO ACTION' THEN '' ELSE COALESCE(CONCAT(' ON UPDATE ',
MAX(RC.UPDATE_RULE)),'') END

, CASE WHEN MAX(RC.DELETE_RULE) = 'NO

ACTION' THEN '' ELSE COALESCE(CONCAT(' ON DELETE ', MAX(RC.DELETE_RULE)),'') END )
FKEY_DEF

FROM DB_COMPARE I

INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA = I.DATABASE_NAME AND
TC.TABLE_NAME = I.TABLE_NAME AND CONSTRAINT_TYPE = 'FOREIGN KEY'

INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE FKEY ON TC.TABLE_SCHEMA =
FKEY.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = FKEY.CONSTRAINT_NAME

LEFT OUTER JOIN

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.CONSTRAINT_SCHEMA =
TC.TABLE_SCHEMA AND RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME

WHERE OBJECT_TYPE = 'FOREIGN KEY' AND

I.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 1

GROUP BY TC.TABLE_SCHEMA, TC.TABLE_NAME,

FKEY.CONSTRAINT_NAME, FKEY.REFERENCED_TABLE_NAME

) FKEY

GROUP BY TABLE_SCHEMA, TABLE_NAME

)FKEY ON FKEY.TABLE_SCHEMA = S.DATABASE_NAME AND

FKEY.TABLE_NAME = S.TABLE_NAME

GROUP BY S.DATABASE_NAME, S.TABLE_NAME

UNION ALL

-- Scripts to alter the existing column, index or constraints

SELECT

CONCAT(' ALTER TABLE `', @DESTINATIONDBNAME , '`.`',S.TABLE_NAME, '` ',

SUBSTRING(CONCAT(COALESCE(CONCAT(",",MAX(C.COL_DEF)),''), COALESCE(CONCAT(', ',
MAX(I.INDEX_DEF)),''), COALESCE(CONCAT(', ', MAX(CHK.CHECK_DEF)), ''),
COALESCE(CONCAT(', ', MAX(FKEY.FKEY_DEF)), '')),2) ,';

') TABLE_DEFINITION

FROM (SELECT DISTINCT S.DATABASE_NAME, S.TABLE_NAME FROM DB_COMPARE S WHERE

S.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 0) S

LEFT OUTER JOIN (

SELECT GROUP_CONCAT( ' CHANGE COLUMN `', COLUMN_NAME,

'` ', '`', COLUMN_NAME, '` ', COLUMN_TYPE

,CASE WHEN COLLATION_NAME IS NOT NULL

THEN CONCAT(' COLLATE ', COLLATION_NAME , ' ') ELSE ' ' END

,CASE WHEN GENERATION_EXPRESSION IS NULL

OR LENGTH(TRIM(GENERATION_EXPRESSION)) = 0 THEN

CONCAT( CASE WHEN IS_NULLABLE

COLLATE utf8_unicode_ci = 'YES' THEN CONCAT(' NULL', ' DEFAULT ',
COALESCE(COLUMN_DEFAULT, 'NULL')) ELSE CONCAT(' NOT NULL', COALESCE(CONCAT('
DEFAULT ', CASE WHEN TRIM(COLUMN_DEFAULT) = '' THEN "''" ELSE CASE WHEN
SUBSTRING(TRIM(COLUMN_DEFAULT),1,1) = '(' THEN COLUMN_DEFAULT ELSE
CONCAT("'",COLUMN_DEFAULT,"'") END END), '') )END, ' ',
UPPER(LTRIM(COALESCE(REPLACE(EXTRA, 'DEFAULT_GENERATED', ''), ''))) )

ELSE

CONCAT(' GENERATED ALWAYS AS (',

GENERATION_EXPRESSION, ') ', CASE EXTRA COLLATE utf8_unicode_ci WHEN 'STORED
GENERATED' THEN ' STORED ' WHEN 'VIRTUAL GENERATED' THEN ' VIRTUAL ' ELSE '' END)

END

ORDER BY C.ORDINAL_POSITION, '

' SEPARATOR ",") COL_DEF

,S.DATABASE_NAME

,S.TABLE_NAME

FROM DB_COMPARE S

LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON

S.DATABASE_NAME = C.TABLE_SCHEMA AND S.TABLE_NAME = C.TABLE_NAME AND S.OBJECT_NAME
= C.COLUMN_NAME

WHERE S.OBJECT_TYPE = 'Column' AND [Link] = 0 AND

S.DATABASE_NAME = @SOURCEDBNAME

GROUP BY S.DATABASE_NAME, S.TABLE_NAME

) C ON C.DATABASE_NAME = S.DATABASE_NAME AND C.TABLE_NAME =

S.TABLE_NAME

LEFT OUTER JOIN

 (

SELECT I.DATABASE_NAME TABLE_SCHEMA

,I.TABLE_NAME

,GROUP_CONCAT(' DROP ', CASE CONSTRAINT_TYPE WHEN

'PRIMARY KEY' THEN 'PRIMARY KEY' WHEN 'UNIQUE' THEN 'CONSTRAINT ' ELSE ' KEY ' END,
OBJECT_NAME, '`, ',

' ADD ', CASE CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN

'PRIMARY' WHEN 'UNIQUE' THEN 'UNIQUE' ELSE '' END, CASE WHEN INDEX_TYPE = 'BTREE'
THEN ' KEY' ELSE CONCAT(' ', INDEX_TYPE, ' KEY ') END, '`', OBJECT_NAME, '` (',
INDEX_COLS, ' )' SEPARATOR "," ) INDEX_DEF

FROM DB_COMPARE I

LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON

TC.TABLE_SCHEMA = I.DATABASE_NAME AND TC.TABLE_NAME = I.TABLE_NAME AND
TC.CONSTRAINT_NAME = I.OBJECT_NAME AND CONSTRAINT_TYPE NOT IN ('CHECK','FOREIGN
KEY')

WHERE I.OBJECT_TYPE = 'Index' AND [Link] = 0 AND

I.DATABASE_NAME = @SOURCEDBNAME

GROUP BY I.DATABASE_NAME, I.TABLE_NAME

)I ON I.TABLE_SCHEMA = S.DATABASE_NAME AND I.TABLE_NAME =

S.TABLE_NAME

LEFT OUTER JOIN

SELECT TABLE_SCHEMA

,TABLE_NAME

,GROUP_CONCAT(CHECK_DEF SEPARATOR ",") CHECK_DEF

FROM (

SELECT TC.TABLE_SCHEMA

,TC.TABLE_NAME

,GROUP_CONCAT(' DROP
CONSTRAINT `', TC.CONSTRAINT_NAME, '`, ',

' ADD CONSTRAINT `', TC.CONSTRAINT_NAME, '`

CHECK (', CHK.CHECK_CLAUSE, ')' SEPARATOR "," ) CHECK_DEF

FROM DB_COMPARE I

INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA = I.DATABASE_NAME AND
TC.TABLE_NAME = I.TABLE_NAME AND CONSTRAINT_TYPE = 'CHECK'
 INNER JOIN
INFORMATION_SCHEMA.CHECK_CONSTRAINTS CHK ON TC.TABLE_SCHEMA = CHK.CONSTRAINT_SCHEMA
AND TC.CONSTRAINT_NAME = CHK.CONSTRAINT_NAME

WHERE OBJECT_TYPE = 'CHECK' AND

I.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 0

GROUP BY TC.TABLE_SCHEMA, TC.TABLE_NAME

)CHK

GROUP BY TABLE_SCHEMA, TABLE_NAME

)CHK ON CHK.TABLE_SCHEMA = S.DATABASE_NAME AND

CHK.TABLE_NAME = S.TABLE_NAME

LEFT OUTER JOIN

SELECT TABLE_SCHEMA

,TABLE_NAME

,GROUP_CONCAT(FKEY_DEF SEPARATOR "," ) FKEY_DEF

FROM (

SELECT TC.TABLE_SCHEMA

,TC.TABLE_NAME

,CONCAT(' DROP CONSTRAINT `',

FKEY.CONSTRAINT_NAME, '`, ',

' ADD CONSTRAINT `', FKEY.CONSTRAINT_NAME, '`

FOREIGN KEY (', GROUP_CONCAT( '`', FKEY.COLUMN_NAME , '`' ORDER BY
FKEY.ORDINAL_POSITION SEPARATOR ","), ') REFERENCES ', '`',
FKEY.REFERENCED_TABLE_NAME, '`(', GROUP_CONCAT( '`', FKEY.REFERENCED_COLUMN_NAME ,
'`' ORDER BY FKEY.ORDINAL_POSITION SEPARATOR ",") , ')'

, CASE WHEN
MAX(RC.UPDATE_RULE) = 'NO ACTION' THEN '' ELSE COALESCE(CONCAT(' ON UPDATE ',
MAX(RC.UPDATE_RULE),'')) END

, CASE WHEN MAX(RC.DELETE_RULE) = 'NO

ACTION' THEN '' ELSE COALESCE(CONCAT(' ON DELETE ', MAX(RC.DELETE_RULE),'')) END )
FKEY_DEF

FROM DB_COMPARE I

INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA = I.DATABASE_NAME AND
TC.TABLE_NAME = I.TABLE_NAME AND CONSTRAINT_TYPE = 'FOREIGN KEY'

INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE FKEY ON TC.TABLE_SCHEMA =
FKEY.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = FKEY.CONSTRAINT_NAME

LEFT OUTER JOIN

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.CONSTRAINT_SCHEMA =
TC.TABLE_SCHEMA AND RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME

WHERE OBJECT_TYPE = 'FOREIGN KEY' AND

I.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 0

GROUP BY TC.TABLE_SCHEMA, TC.TABLE_NAME,

FKEY.CONSTRAINT_NAME, FKEY.REFERENCED_TABLE_NAME

) FKEY

GROUP BY TABLE_SCHEMA, TABLE_NAME

)FKEY ON FKEY.TABLE_SCHEMA = S.DATABASE_NAME AND

FKEY.TABLE_NAME = S.TABLE_NAME

GROUP BY S.DATABASE_NAME, S.TABLE_NAME

UNION ALL

-- Drop the index, connstraints and columnns which are existing onny in destination

SELECT

CONCAT(' ALTER TABLE `', @DESTINATIONDBNAME , '`.`',S.TABLE_NAME, '` ',

SUBSTRING(CONCAT(COALESCE(CONCAT(",",MAX(C.COL_DEF)),''),
COALESCE(CONCAT(",",MAX(I.INDEX_DEF)),''),COALESCE(CONCAT(",",MAX(CHK.CHECK_DEF)),
'')),2) ,';') TABLE_DEFINITION

FROM (

SELECT DISTINCT S.DATABASE_NAME, S.TABLE_NAME

FROM DB_COMPARE S

WHERE S.DATABASE_NAME = @DESTINATIONDBNAME AND [Link] = 1 AND

OBJECT_TYPE <> 'Table'

AND NOT EXISTS ( SELECT 1 FROM DB_COMPARE D WHERE

S.DATABASE_NAME = D.DATABASE_NAME AND S.TABLE_NAME = D.TABLE_NAME AND D.OBJECT_TYPE
= 'Table')

) S

LEFT OUTER JOIN (

 SELECT GROUP_CONCAT( 'DROP COLUMN `', OBJECT_NAME,
'`' SEPARATOR ",") COL_DEF

,S.DATABASE_NAME

,S.TABLE_NAME

FROM DB_COMPARE S

WHERE S.OBJECT_TYPE = 'Column' AND [Link] = 1 AND

S.DATABASE_NAME = @DESTINATIONDBNAME

GROUP BY S.DATABASE_NAME, S.TABLE_NAME

) C ON C.DATABASE_NAME = S.DATABASE_NAME AND C.TABLE_NAME =

S.TABLE_NAME

LEFT OUTER JOIN

SELECT I.DATABASE_NAME TABLE_SCHEMA

,I.TABLE_NAME

,GROUP_CONCAT('DROP ', CASE CONSTRAINT_TYPE WHEN

'PRIMARY KEY' THEN 'PRIMARY KEY ' WHEN 'UNIQUE' THEN 'CONSTRAINT ' ELSE ' KEY '
END, CASE CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN '' ELSE
CONCAT('`',OBJECT_NAME, '`' ) END SEPARATOR "," ) INDEX_DEF

FROM DB_COMPARE I

LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON

TC.TABLE_SCHEMA = I.DATABASE_NAME AND TC.TABLE_NAME = I.TABLE_NAME AND
TC.CONSTRAINT_NAME = I.OBJECT_NAME AND CONSTRAINT_TYPE NOT IN ('CHECK','FOREIGN
KEY')

WHERE I.OBJECT_TYPE = 'Index' AND [Link] = 1 AND

I.DATABASE_NAME = @DESTINATIONDBNAME

GROUP BY I.DATABASE_NAME, I.TABLE_NAME

)I ON I.TABLE_SCHEMA = S.DATABASE_NAME AND I.TABLE_NAME =

S.TABLE_NAME

LEFT OUTER JOIN

SELECT TABLE_SCHEMA

,TABLE_NAME

,GROUP_CONCAT(CHECK_DEF SEPARATOR ",") CHECK_DEF

FROM (

SELECT TC.TABLE_SCHEMA
 ,TC.TABLE_NAME

,GROUP_CONCAT('DROP
CONSTRAINT `', TC.CONSTRAINT_NAME, '`, ' SEPARATOR "," ) CHECK_DEF

FROM DB_COMPARE I

INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA = I.DATABASE_NAME AND
TC.TABLE_NAME = I.TABLE_NAME AND CONSTRAINT_TYPE IN ('CHECK', 'FOREIGN KEY')

WHERE OBJECT_TYPE = 'CHECK' AND

I.DATABASE_NAME = @DESTINATIONDBNAME AND [Link] = 1

GROUP BY TC.TABLE_SCHEMA, TC.TABLE_NAME

)CHK

GROUP BY TABLE_SCHEMA, TABLE_NAME

)CHK ON CHK.TABLE_SCHEMA = S.DATABASE_NAME AND

CHK.TABLE_NAME = S.TABLE_NAME

GROUP BY S.DATABASE_NAME, S.TABLE_NAME

END //

DELIMITER ;

DROP PROCEDURE IF EXISTS SP_DBSCHEMACOMPARE_CROSS_CLUSTER;

DELIMITER //

CREATE PROCEDURE SP_DBSCHEMACOMPARE_CROSS_CLUSTER (SOURCEDBNAME VARCHAR(64),

DESTINATIONDBNAME VARCHAR(64))

BEGIN

SET @SOURCEDBNAME = SOURCEDBNAME COLLATE utf8_tolower_ci, @DESTINATIONDBNAME =

DESTINATIONDBNAME COLLATE utf8_tolower_ci;

DROP TABLE IF EXISTS DB_COMPARE;

CREATE TABLE IF NOT EXISTS DB_COMPARE

(

DATABASE_NAME VARCHAR(64) COLLATE utf8_tolower_ci

,TABLE_NAME VARCHAR(64) COLLATE utf8_tolower_ci

,OBJECT_NAME VARCHAR(64) COLLATE utf8_tolower_ci

,OBJECT_TYPE VARCHAR(64) COLLATE utf8_tolower_ci

,MESSAGE VARCHAR(255) COLLATE utf8_tolower_ci

,ISMISSING BIT

,INDEX_COLS VARCHAR(2000) COLLATE utf8_tolower_ci

,INDEX_TYPE VARCHAR(100) COLLATE utf8_tolower_ci

,KEY IX_TYPE (OBJECT_TYPE)

,KEY IX_NAME (DATABASE_NAME, TABLE_NAME)

);

-- Gets the tables exist only in source

INSERT INTO DB_COMPARE

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, NULL, 'Table', CONCAT('Table exists only in

source database `', S.TABLE_SCHEMA, '`.`', S.TABLE_NAME, '`'), 1, NULL, NULL

FROM `tables_backup`.information_schema_tables S

LEFT OUTER JOIN `tables_backup`.information_schema_tables D ON D.TABLE_SCHEMA =

@DESTINATIONDBNAME AND S.TABLE_NAME = D.TABLE_NAME

WHERE S.TABLE_SCHEMA = @SOURCEDBNAME

AND D.TABLE_NAME IS NULL;

-- Gets the table exist only in destination

INSERT INTO DB_COMPARE

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, NULL, 'Table', CONCAT('Table exists only in

destination database `', S.TABLE_SCHEMA, '`.`', S.TABLE_NAME, '`'), 1, NULL, NULL

FROM `tables_backup`.information_schema_tables S

LEFT OUTER JOIN `tables_backup`.information_schema_tables D ON D.TABLE_SCHEMA =

@SOURCEDBNAME AND S.TABLE_NAME = D.TABLE_NAME

WHERE S.TABLE_SCHEMA = @DESTINATIONDBNAME

 AND D.TABLE_NAME IS NULL;

-- Gets the columns, which are not part of any missing table, exist in source

INSERT INTO DB_COMPARE

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.COLUMN_NAME, 'Column', CONCAT('Column exists

only in source database `', S.TABLE_SCHEMA, '`.`', S.TABLE_NAME, '`'), 1, NULL,
NULL

FROM `tables_backup`.INFORMATION_SCHEMA_COLUMNS S

INNER JOIN `tables_backup`.information_schema_tables T ON S.TABLE_NAME =

T.TABLE_NAME AND T.TABLE_SCHEMA = @DESTINATIONDBNAME

LEFT OUTER JOIN `tables_backup`.INFORMATION_SCHEMA_COLUMNS D ON D.TABLE_SCHEMA =

@DESTINATIONDBNAME AND S.TABLE_NAME = D.TABLE_NAME AND S.COLUMN_NAME =
D.COLUMN_NAME

WHERE S.TABLE_SCHEMA = @SOURCEDBNAME

AND D.COLUMN_NAME IS NULL;

-- Gets the columns, which are not part of any missing table, exist in destination

INSERT INTO DB_COMPARE

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.COLUMN_NAME, 'Column', CONCAT('Column exists

only in destination database `', S.TABLE_SCHEMA, '`.`', S.TABLE_NAME, '`'), 1,
NULL, NULL

FROM `tables_backup`.INFORMATION_SCHEMA_COLUMNS S

INNER JOIN `tables_backup`.information_schema_tables T ON S.TABLE_NAME =

T.TABLE_NAME AND T.TABLE_SCHEMA = @SOURCEDBNAME

LEFT OUTER JOIN `tables_backup`.INFORMATION_SCHEMA_COLUMNS D ON D.TABLE_SCHEMA =

@SOURCEDBNAME AND S.TABLE_NAME = D.TABLE_NAME AND S.COLUMN_NAME = D.COLUMN_NAME

WHERE S.TABLE_SCHEMA = @DESTINATIONDBNAME

AND D.COLUMN_NAME IS NULL;

-- Get entries for change in column

-- The OR conditions shall be replaced by SHA2 HASHING

INSERT INTO DB_COMPARE

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.COLUMN_NAME, 'Column', CONCAT('Column

property is changed `', S.TABLE_SCHEMA, '`.`', S.TABLE_NAME, '`'), 0, NULL, NULL

FROM `tables_backup`.INFORMATION_SCHEMA_COLUMNS S
INNER JOIN `tables_backup`.INFORMATION_SCHEMA_COLUMNS D ON D.TABLE_SCHEMA =
@DESTINATIONDBNAME AND S.TABLE_NAME = D.TABLE_NAME AND S.COLUMN_NAME =
D.COLUMN_NAME

WHERE S.TABLE_SCHEMA = @SOURCEDBNAME

AND

COALESCE(S.COLUMN_DEFAULT, '') <>

COALESCE(D.COLUMN_DEFAULT, '')

OR COALESCE(S.IS_NULLABLE , '') <> COALESCE(D.IS_NULLABLE , '')

OR COALESCE(S.CHARACTER_SET_NAME,'') <>
COALESCE(D.CHARACTER_SET_NAME,'')

OR COALESCE(S.COLUMN_TYPE, '') <> COALESCE(D.COLUMN_TYPE,'')

OR COALESCE([Link],'') <> COALESCE([Link],'')

OR COALESCE(S.COLUMN_COMMENT,'') <> COALESCE(D.COLUMN_COMMENT,'')

OR COALESCE(S.GENERATION_EXPRESSION,'') <>
COALESCE(D.GENERATION_EXPRESSION,'')

);

-- AND SHA2(CONCAT(S.COLUMN_DEFAULT, description), 256) =

SHA2(CONCAT(name, description), 256);

-- Gets the Indexes, which are not associated with constraints, exist only in
destination

INSERT INTO DB_COMPARE

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME, 'Index', CONCAT('Inded exists

only in destination database `', S.TABLE_SCHEMA, '`.`', S.TABLE_NAME, '`'), 1,
GROUP_CONCAT( "`", COALESCE(S.COLUMN_NAME ,[Link] ), "`", CASE WHEN
[Link] = 'D' THEN ' DESC ' ELSE '' END, COALESCE(CONCAT(' COMMENT',
NULLIF(TRIM([Link]),'') ), '') , CASE WHEN S.IS_VISIBLE = 'NO' THEN '
INVISIBLE ' ELSE '' END ORDER BY S.SEQ_IN_INDEX ASC SEPARATOR "," ) INDEX_COLS,
S.INDEX_TYPE

FROM `tables_backup`.INFORMATION_SCHEMA_STATISTICS S

INNER JOIN `tables_backup`.information_schema_tables T ON S.TABLE_NAME =

T.TABLE_NAME AND T.TABLE_SCHEMA = @SOURCEDBNAME

LEFT OUTER JOIN `tables_backup`.INFORMATION_SCHEMA_STATISTICS D ON D.TABLE_SCHEMA =

@SOURCEDBNAME AND S.TABLE_NAME = D.TABLE_NAME AND S.INDEX_NAME = D.INDEX_NAME
WHERE S.TABLE_SCHEMA = @DESTINATIONDBNAME

-- AND S.NON_UNIQUE = 1

AND D.INDEX_NAME IS NULL

GROUP BY S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME, S.INDEX_TYPE;

-- Gets the Indexes, which are not associated with constraints, exist only in
source

INSERT INTO DB_COMPARE

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME, 'Index', CONCAT('Inded exists

only in source database `', S.TABLE_SCHEMA, '`.`', S.TABLE_NAME, '`'), 1,
GROUP_CONCAT( "`", COALESCE(S.COLUMN_NAME ,[Link] ), "`", CASE WHEN
[Link] = 'D' THEN ' DESC ' ELSE '' END, COALESCE(CONCAT(' COMMENT',
NULLIF(TRIM([Link]),'') ), '') , CASE WHEN S.IS_VISIBLE = 'NO' THEN '
INVISIBLE ' ELSE '' END ORDER BY S.SEQ_IN_INDEX ASC SEPARATOR "," ) INDEX_COLS,
S.INDEX_TYPE

FROM `tables_backup`.INFORMATION_SCHEMA_STATISTICS S

INNER JOIN `tables_backup`.information_schema_tables T ON S.TABLE_NAME =

T.TABLE_NAME AND T.TABLE_SCHEMA = @DESTINATIONDBNAME

LEFT OUTER JOIN `tables_backup`.INFORMATION_SCHEMA_STATISTICS D ON D.TABLE_SCHEMA =

@DESTINATIONDBNAME AND S.TABLE_NAME = D.TABLE_NAME AND S.INDEX_NAME = D.INDEX_NAME

WHERE S.TABLE_SCHEMA = @SOURCEDBNAME

-- AND S.NON_UNIQUE = 1

AND D.INDEX_NAME IS NULL

GROUP BY S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME, S.INDEX_TYPE;

-- Gets the Indexes, which are not associated with constraints, exist in both
databases but with difference in its properties

INSERT INTO DB_COMPARE

SELECT S.*

FROM (

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME, 'Index',

CONCAT('Index with change in properties `', S.TABLE_SCHEMA, '`.`', S.TABLE_NAME,
'`'), 0

, GROUP_CONCAT( "`", COALESCE(S.COLUMN_NAME ,[Link] ), "`",

CASE WHEN [Link] = 'D' THEN ' DESC ' ELSE '' END, COALESCE(CONCAT(' COMMENT',
NULLIF(TRIM([Link]),'') ), '') , CASE WHEN S.IS_VISIBLE = 'NO' THEN '
INVISIBLE ' ELSE '' END ORDER BY S.SEQ_IN_INDEX ASC SEPARATOR "," ) INDEX_COLS,
S.INDEX_TYPE

FROM `tables_backup`.INFORMATION_SCHEMA_STATISTICS S

WHERE S.TABLE_SCHEMA = @SOURCEDBNAME

GROUP BY S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME, S.INDEX_TYPE

)S

INNER JOIN

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME

,GROUP_CONCAT( "`",
COALESCE(S.COLUMN_NAME ,[Link] ), "`", CASE WHEN [Link] = 'D' THEN '
DESC ' ELSE '' END, COALESCE(CONCAT(' COMMENT', NULLIF(TRIM([Link] COLLATE
utf8_unicode_ci),'') ), '') , CASE WHEN S.IS_VISIBLE = 'NO' THEN ' INVISIBLE '
ELSE '' END ORDER BY S.SEQ_IN_INDEX ASC SEPARATOR "," ) INDEX_COLS

FROM `tables_backup`.INFORMATION_SCHEMA_STATISTICS S

WHERE S.TABLE_SCHEMA = @DESTINATIONDBNAME

GROUP BY S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME

) D ON S.TABLE_NAME = D.TABLE_NAME

AND S.INDEX_NAME = D.INDEX_NAME

AND S.INDEX_COLS <> D.INDEX_COLS;

-- Gets missing check constraint from source database

INSERT INTO DB_COMPARE (DATABASE_NAME, TABLE_NAME, OBJECT_NAME, OBJECT_TYPE,

MESSAGE, ISMISSING)

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.CONSTRAINT_NAME, S.CONSTRAINT_TYPE, CONCAT('

Missing ', S.CONSTRAINT_TYPE, '( ', S.CONSTRAINT_NAME, ') in source database'), 1

FROM `tables_backup`.INFORMATION_SCHEMA_TABLE_CONSTRAINTS S

INNER JOIN `tables_backup`.information_schema_tables T ON S.TABLE_NAME =

T.TABLE_NAME AND T.TABLE_SCHEMA = @DESTINATIONDBNAME

LEFT OUTER JOIN `tables_backup`.INFORMATION_SCHEMA_TABLE_CONSTRAINTS D ON

D.TABLE_SCHEMA = @DESTINATIONDBNAME AND S.TABLE_NAME = D.TABLE_NAME AND
S.CONSTRAINT_NAME = D.CONSTRAINT_NAME AND S.CONSTRAINT_TYPE = D.CONSTRAINT_TYPE

WHERE S.TABLE_SCHEMA = @SOURCEDBNAME

AND D.CONSTRAINT_NAME IS NULL;

-- Gets missing check constraint from destination database

INSERT INTO DB_COMPARE (DATABASE_NAME, TABLE_NAME, OBJECT_NAME, OBJECT_TYPE,

MESSAGE, ISMISSING)

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.CONSTRAINT_NAME, S.CONSTRAINT_TYPE, CONCAT('

Missing ', S.CONSTRAINT_TYPE, '( ', S.CONSTRAINT_NAME, ') in destination
database'), 1

FROM `tables_backup`.INFORMATION_SCHEMA_TABLE_CONSTRAINTS S

INNER JOIN `tables_backup`.information_schema_tables T ON S.TABLE_NAME =

T.TABLE_NAME AND T.TABLE_SCHEMA = @SOURCEDBNAME

LEFT OUTER JOIN `tables_backup`.INFORMATION_SCHEMA_TABLE_CONSTRAINTS D ON

D.TABLE_SCHEMA = @SOURCEDBNAME AND S.TABLE_NAME = D.TABLE_NAME AND
S.CONSTRAINT_NAME = D.CONSTRAINT_NAME AND S.CONSTRAINT_TYPE = D.CONSTRAINT_TYPE

WHERE S.TABLE_SCHEMA = @DESTINATIONDBNAME

AND D.CONSTRAINT_NAME IS NULL;

INSERT INTO DB_COMPARE

SELECT S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME, 'Table', 'Missing Table

Indexes', 1 , GROUP_CONCAT( "`", COALESCE(S.COLUMN_NAME ,[Link] ), "`", CASE
WHEN [Link] = 'D' THEN ' DESC ' ELSE '' END, COALESCE(CONCAT(' COMMENT',
NULLIF(TRIM([Link] COLLATE utf8_unicode_ci),'') ), '') , CASE WHEN
S.IS_VISIBLE = 'NO' THEN ' INVISIBLE ' ELSE '' END ORDER BY S.SEQ_IN_INDEX ASC
SEPARATOR "," ) INDEX_COLS, S.INDEX_TYPE

FROM `tables_backup`.INFORMATION_SCHEMA_STATISTICS S

INNER JOIN DB_COMPARE D ON S.TABLE_SCHEMA = D.DATABASE_NAME AND S.TABLE_NAME =

D.TABLE_NAME

WHERE D.OBJECT_TYPE = 'Table'

GROUP BY S.TABLE_SCHEMA, S.TABLE_NAME, S.INDEX_NAME, INDEX_TYPE;

-- Generate Table Schema for the tables exist only in source

SELECT A.*
FROM (

SELECT

CONCAT(' CREATE TABLE IF NOT EXISTS `',

@DESTINATIONDBNAME , '`.`',S.TABLE_NAME, '` (', GROUP_CONCAT( '`', COLUMN_NAME, '`
', COLUMN_TYPE

,CASE WHEN COLLATION_NAME IS NOT NULL

THEN CONCAT(' COLLATE ', COLLATION_NAME , ' ') ELSE ' ' END

,CASE WHEN GENERATION_EXPRESSION IS NULL

OR LENGTH(TRIM(GENERATION_EXPRESSION)) = 0 THEN

CONCAT( CASE WHEN IS_NULLABLE =

'YES' THEN CONCAT(' NULL', ' DEFAULT ', COALESCE(COLUMN_DEFAULT, 'NULL')) ELSE
CONCAT(' NOT NULL', COALESCE(CONCAT(' DEFAULT ', CASE WHEN TRIM(COLUMN_DEFAULT) =
'' THEN "''" ELSE CASE WHEN SUBSTRING(TRIM(COLUMN_DEFAULT),1,1) = '(' THEN
COLUMN_DEFAULT ELSE CONCAT("'",COLUMN_DEFAULT,"'") END END), '') )END, ' ',
UPPER(LTRIM(COALESCE(REPLACE(EXTRA, 'DEFAULT_GENERATED', ''), ''))) )

ELSE

CONCAT(' GENERATED ALWAYS AS (',

GENERATION_EXPRESSION, ') ', CASE EXTRA WHEN 'STORED GENERATED' THEN ' STORED '
WHEN 'VIRTUAL GENERATED' THEN ' VIRTUAL ' ELSE '' END)

END

ORDER BY C.ORDINAL_POSITION, '

' SEPARATOR ","), COALESCE(CONCAT(', ',

MAX(I.INDEX_DEF)),''), COALESCE(CONCAT(', ', MAX(CHK.CHECK_DEF)), ''),
COALESCE(CONCAT(', ', MAX(FKEY.FKEY_DEF)), '') ,') ENGINE=InnoDB DEFAULT
CHARSET=utf8 COLLATE=utf8_unicode_ci;

') TABLE_DEFINITION

FROM DB_COMPARE S

INNER JOIN `tables_backup`.INFORMATION_SCHEMA_COLUMNS C ON

S.DATABASE_NAME = C.TABLE_SCHEMA AND S.TABLE_NAME = C.TABLE_NAME AND S.OBJECT_NAME
IS NULL

LEFT OUTER JOIN

SELECT I.DATABASE_NAME TABLE_SCHEMA

,I.TABLE_NAME

,GROUP_CONCAT(CASE
CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN 'PRIMARY' WHEN 'UNIQUE' THEN 'UNIQUE' ELSE
'' END, CASE WHEN INDEX_TYPE = 'BTREE' THEN ' KEY' ELSE CONCAT(' ', INDEX_TYPE, '
KEY ') END, '`', OBJECT_NAME, '` (', INDEX_COLS, ' )' SEPARATOR "," ) INDEX_DEF
 FROM DB_COMPARE I

LEFT JOIN
`tables_backup`.INFORMATION_SCHEMA_TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA =
I.DATABASE_NAME AND TC.TABLE_NAME = I.TABLE_NAME AND TC.CONSTRAINT_NAME =
I.OBJECT_NAME

AND CONSTRAINT_TYPE NOT

IN ('CHECK','FOREIGN KEY')

WHERE I.OBJECT_TYPE = 'Table' AND

I.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 1 AND I.OBJECT_NAME IS NOT NULL

GROUP BY I.DATABASE_NAME, I.TABLE_NAME

)I ON I.TABLE_SCHEMA = S.DATABASE_NAME AND

I.TABLE_NAME = S.TABLE_NAME

LEFT OUTER JOIN

SELECT TABLE_SCHEMA

,TABLE_NAME

,GROUP_CONCAT(CHECK_DEF
SEPARATOR ",") CHECK_DEF

FROM (

SELECT TC.TABLE_SCHEMA

,TC.TABLE_NAME

,GROUP_CONCAT('
CONSTRAINT `', TC.CONSTRAINT_NAME, '` CHECK (', CHK.CHECK_CLAUSE, ')' SEPARATOR
"," ) CHECK_DEF

FROM DB_COMPARE I

INNER JOIN
`tables_backup`.INFORMATION_SCHEMA_TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA =
I.DATABASE_NAME AND TC.TABLE_NAME = I.TABLE_NAME AND CONSTRAINT_TYPE = 'CHECK'

INNER JOIN
`tables_backup`.INFORMATION_SCHEMA_CHECK_CONSTRAINTS CHK ON TC.TABLE_SCHEMA =
CHK.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = CHK.CONSTRAINT_NAME

WHERE OBJECT_TYPE = 'Table'

AND I.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 1 AND I.OBJECT_NAME IS NULL

GROUP BY TC.TABLE_SCHEMA,
TC.TABLE_NAME

)CHK

GROUP BY TABLE_SCHEMA ,TABLE_NAME

 )CHK ON CHK.TABLE_SCHEMA = S.DATABASE_NAME AND
CHK.TABLE_NAME = S.TABLE_NAME

LEFT OUTER JOIN

SELECT TABLE_SCHEMA

,TABLE_NAME

,GROUP_CONCAT(FKEY_DEF
SEPARATOR "," ) FKEY_DEF

FROM (

SELECT TC.TABLE_SCHEMA

,TC.TABLE_NAME

,CONCAT('
CONSTRAINT `', FKEY.CONSTRAINT_NAME, '` FOREIGN KEY (', GROUP_CONCAT( '`',
FKEY.COLUMN_NAME , '`' ORDER BY FKEY.ORDINAL_POSITION SEPARATOR ","), ') REFERENCES
', '`', FKEY.REFERENCED_TABLE_NAME, '`(', GROUP_CONCAT( '`',
FKEY.REFERENCED_COLUMN_NAME , '`' ORDER BY FKEY.ORDINAL_POSITION SEPARATOR ",") ,
')'

, CASE
WHEN MAX(RC.UPDATE_RULE) = 'NO ACTION' THEN '' ELSE COALESCE(CONCAT(' ON UPDATE ',
MAX(RC.UPDATE_RULE)),'') END

, CASE
WHEN MAX(RC.DELETE_RULE) = 'NO ACTION' THEN '' ELSE COALESCE(CONCAT(' ON DELETE ',
MAX(RC.DELETE_RULE)),'') END ) FKEY_DEF

FROM DB_COMPARE I

INNER JOIN
`tables_backup`.INFORMATION_SCHEMA_TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA =
I.DATABASE_NAME AND TC.TABLE_NAME = I.TABLE_NAME AND CONSTRAINT_TYPE = 'FOREIGN
KEY'

INNER JOIN
`tables_backup`.INFORMATION_SCHEMA_KEY_COLUMN_USAGE FKEY ON TC.TABLE_SCHEMA =
FKEY.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = FKEY.CONSTRAINT_NAME

LEFT OUTER JOIN

`tables_backup`.INFORMATION_SCHEMA_REFERENTIAL_CONSTRAINTS RC ON
RC.CONSTRAINT_SCHEMA = TC.TABLE_SCHEMA AND RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME

WHERE OBJECT_TYPE = 'Table'

AND I.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 1 AND I.OBJECT_NAME IS NULL

GROUP BY TC.TABLE_SCHEMA,
TC.TABLE_NAME, FKEY.CONSTRAINT_NAME, FKEY.REFERENCED_TABLE_NAME
 ) FKEY

GROUP BY TABLE_SCHEMA, TABLE_NAME

)FKEY ON FKEY.TABLE_SCHEMA = S.DATABASE_NAME

AND FKEY.TABLE_NAME = S.TABLE_NAME

WHERE OBJECT_TYPE = 'Table' AND S.DATABASE_NAME = @SOURCEDBNAME AND

[Link] = 1

GROUP BY S.DATABASE_NAME, S.TABLE_NAME

) A

UNION ALL

-- select tables that exists only in destination

SELECT CONCAT(' DROP TABLE IF EXISTS `', @DESTINATIONDBNAME , '`.`', S.TABLE_NAME,

'`;

')

FROM DB_COMPARE S

WHERE OBJECT_TYPE = 'Table' AND S.DATABASE_NAME = @DESTINATIONDBNAME AND

[Link] = 1

UNION ALL

-- Generate script for missing columns, index and other constraints in Source

SELECT

CONCAT(' ALTER TABLE `', @DESTINATIONDBNAME , '`.`',S.TABLE_NAME, '` ',

SUBSTRING(CONCAT(COALESCE(CONCAT(",",MAX(C.COL_DEF)), ''), COALESCE(CONCAT(', ',
MAX(I.INDEX_DEF)),''), COALESCE(CONCAT(', ', MAX(CHK.CHECK_DEF)), ''),
COALESCE(CONCAT(', ', MAX(FKEY.FKEY_DEF)), '')),2) ,';

') TABLE_DEFINITION

FROM (SELECT DISTINCT S.DATABASE_NAME, S.TABLE_NAME FROM DB_COMPARE S WHERE

S.OBJECT_TYPE <> 'Table' AND S.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 1)
S

LEFT OUTER JOIN (

 SELECT GROUP_CONCAT( ' ADD COLUMN `', COLUMN_NAME, '`
', COLUMN_TYPE

,CASE WHEN COLLATION_NAME IS NOT NULL

THEN CONCAT(' COLLATE ', COLLATION_NAME , ' ') ELSE ' ' END

,CASE WHEN GENERATION_EXPRESSION IS NULL

OR LENGTH(TRIM(GENERATION_EXPRESSION)) = 0 THEN

CONCAT( CASE WHEN IS_NULLABLE =

'YES' THEN CONCAT(' NULL', ' DEFAULT ', COALESCE(COLUMN_DEFAULT, 'NULL')) ELSE
CONCAT(' NOT NULL', COALESCE(CONCAT(' DEFAULT ', CASE WHEN TRIM(COLUMN_DEFAULT) =
'' THEN "''" ELSE CASE WHEN SUBSTRING(TRIM(COLUMN_DEFAULT),1,1) = '(' THEN
COLUMN_DEFAULT ELSE CONCAT("'",COLUMN_DEFAULT,"'") END END), '') )END, ' ',
UPPER(LTRIM(COALESCE(REPLACE(EXTRA, 'DEFAULT_GENERATED', ''), ''))) )

ELSE

CONCAT(' GENERATED ALWAYS AS (',

GENERATION_EXPRESSION, ') ', CASE EXTRA WHEN 'STORED GENERATED' THEN ' STORED '
WHEN 'VIRTUAL GENERATED' THEN ' VIRTUAL ' ELSE '' END)

END

ORDER BY C.ORDINAL_POSITION, '

' SEPARATOR ",") COL_DEF

,S.DATABASE_NAME

,S.TABLE_NAME

FROM DB_COMPARE S

LEFT JOIN `tables_backup`.INFORMATION_SCHEMA_COLUMNS

C ON S.DATABASE_NAME = C.TABLE_SCHEMA AND S.TABLE_NAME = C.TABLE_NAME AND
S.OBJECT_NAME = C.COLUMN_NAME

WHERE S.OBJECT_TYPE = 'Column' AND [Link] = 1 AND

S.DATABASE_NAME = @SOURCEDBNAME

GROUP BY S.DATABASE_NAME, S.TABLE_NAME

) C ON C.DATABASE_NAME = S.DATABASE_NAME AND C.TABLE_NAME =

S.TABLE_NAME

LEFT OUTER JOIN

SELECT I.DATABASE_NAME TABLE_SCHEMA

,I.TABLE_NAME

,GROUP_CONCAT(' ADD ', CASE CONSTRAINT_TYPE WHEN

'PRIMARY KEY' THEN 'PRIMARY' WHEN 'UNIQUE' THEN 'UNIQUE' ELSE '' END, CASE WHEN
INDEX_TYPE = 'BTREE' THEN ' KEY' ELSE CONCAT(' ', INDEX_TYPE, ' KEY ') END, '`',
OBJECT_NAME, '` (', INDEX_COLS, ' )' SEPARATOR "," ) INDEX_DEF
 FROM DB_COMPARE I

LEFT OUTER JOIN

`tables_backup`.INFORMATION_SCHEMA_TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA =
I.DATABASE_NAME AND TC.TABLE_NAME = I.TABLE_NAME AND TC.CONSTRAINT_NAME =
I.OBJECT_NAME AND CONSTRAINT_TYPE NOT IN ('CHECK','FOREIGN KEY')

WHERE I.OBJECT_TYPE = 'Index' AND [Link] = 1 AND

I.DATABASE_NAME = @SOURCEDBNAME

GROUP BY I.DATABASE_NAME, I.TABLE_NAME

)I ON I.TABLE_SCHEMA = S.DATABASE_NAME AND I.TABLE_NAME =

S.TABLE_NAME

LEFT OUTER JOIN

SELECT TABLE_SCHEMA

,TABLE_NAME

,GROUP_CONCAT(CHECK_DEF SEPARATOR ",") CHECK_DEF

FROM (

SELECT TC.TABLE_SCHEMA

,TC.TABLE_NAME

,GROUP_CONCAT(' ADD
CONSTRAINT `', TC.CONSTRAINT_NAME, '` CHECK (', CHK.CHECK_CLAUSE, ')' SEPARATOR
"," ) CHECK_DEF

FROM DB_COMPARE I

INNER JOIN
`tables_backup`.INFORMATION_SCHEMA_TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA =
I.DATABASE_NAME AND TC.TABLE_NAME = I.TABLE_NAME AND CONSTRAINT_TYPE = 'CHECK'

INNER JOIN
`tables_backup`.INFORMATION_SCHEMA_CHECK_CONSTRAINTS CHK ON TC.TABLE_SCHEMA =
CHK.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = CHK.CONSTRAINT_NAME

WHERE OBJECT_TYPE = 'CHECK' AND

I.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 1

GROUP BY TC.TABLE_SCHEMA, TC.TABLE_NAME

)CHK

GROUP BY TABLE_SCHEMA, TABLE_NAME

)CHK ON CHK.TABLE_SCHEMA = S.DATABASE_NAME AND

CHK.TABLE_NAME = S.TABLE_NAME
LEFT OUTER JOIN

SELECT TABLE_SCHEMA

,TABLE_NAME

,GROUP_CONCAT(FKEY_DEF SEPARATOR "," ) FKEY_DEF

FROM (

SELECT TC.TABLE_SCHEMA

,TC.TABLE_NAME

,CONCAT(' ADD CONSTRAINT `',

FKEY.CONSTRAINT_NAME, '` FOREIGN KEY (', GROUP_CONCAT( '`', FKEY.COLUMN_NAME , '`'
ORDER BY FKEY.ORDINAL_POSITION SEPARATOR ","), ') REFERENCES ', '`',
FKEY.REFERENCED_TABLE_NAME, '`(', GROUP_CONCAT( '`', FKEY.REFERENCED_COLUMN_NAME ,
'`' ORDER BY FKEY.ORDINAL_POSITION SEPARATOR ",") , ')'

, CASE WHEN
MAX(RC.UPDATE_RULE) = 'NO ACTION' THEN '' ELSE COALESCE(CONCAT(' ON UPDATE ',
MAX(RC.UPDATE_RULE)),'') END

, CASE WHEN MAX(RC.DELETE_RULE) = 'NO

ACTION' THEN '' ELSE COALESCE(CONCAT(' ON DELETE ', MAX(RC.DELETE_RULE)),'') END )
FKEY_DEF

FROM DB_COMPARE I

INNER JOIN
`tables_backup`.INFORMATION_SCHEMA_TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA =
I.DATABASE_NAME AND TC.TABLE_NAME = I.TABLE_NAME AND CONSTRAINT_TYPE = 'FOREIGN
KEY'

INNER JOIN
`tables_backup`.INFORMATION_SCHEMA_KEY_COLUMN_USAGE FKEY ON TC.TABLE_SCHEMA =
FKEY.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = FKEY.CONSTRAINT_NAME

LEFT OUTER JOIN

`tables_backup`.INFORMATION_SCHEMA_REFERENTIAL_CONSTRAINTS RC ON
RC.CONSTRAINT_SCHEMA = TC.TABLE_SCHEMA AND RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME

WHERE OBJECT_TYPE = 'FOREIGN KEY' AND

I.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 1

GROUP BY TC.TABLE_SCHEMA, TC.TABLE_NAME,

FKEY.CONSTRAINT_NAME, FKEY.REFERENCED_TABLE_NAME

) FKEY

GROUP BY TABLE_SCHEMA, TABLE_NAME

)FKEY ON FKEY.TABLE_SCHEMA = S.DATABASE_NAME AND

FKEY.TABLE_NAME = S.TABLE_NAME
GROUP BY S.DATABASE_NAME, S.TABLE_NAME

UNION ALL

-- Scripts to alter the existing column, index or constraints

SELECT

CONCAT(' ALTER TABLE `', @DESTINATIONDBNAME , '`.`',S.TABLE_NAME, '` ',

SUBSTRING(CONCAT(COALESCE(CONCAT(",",MAX(C.COL_DEF)),''), COALESCE(CONCAT(', ',
MAX(I.INDEX_DEF)),''), COALESCE(CONCAT(', ', MAX(CHK.CHECK_DEF)), ''),
COALESCE(CONCAT(', ', MAX(FKEY.FKEY_DEF)), '')),2) ,';

') TABLE_DEFINITION

FROM (SELECT DISTINCT S.DATABASE_NAME, S.TABLE_NAME FROM DB_COMPARE S WHERE

S.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 0) S

LEFT OUTER JOIN (

SELECT GROUP_CONCAT( ' CHANGE COLUMN `', COLUMN_NAME,

'` ', '`', COLUMN_NAME, '` ', COLUMN_TYPE

,CASE WHEN COLLATION_NAME IS NOT NULL

THEN CONCAT(' COLLATE ', COLLATION_NAME , ' ') ELSE ' ' END

,CASE WHEN GENERATION_EXPRESSION IS NULL

OR LENGTH(TRIM(GENERATION_EXPRESSION)) = 0 THEN

CONCAT( CASE WHEN IS_NULLABLE =

'YES' THEN CONCAT(' NULL', ' DEFAULT ', COALESCE(COLUMN_DEFAULT, 'NULL')) ELSE
CONCAT(' NOT NULL', COALESCE(CONCAT(' DEFAULT ', CASE WHEN TRIM(COLUMN_DEFAULT) =
'' THEN "''" ELSE CASE WHEN SUBSTRING(TRIM(COLUMN_DEFAULT),1,1) = '(' THEN
COLUMN_DEFAULT ELSE CONCAT("'",COLUMN_DEFAULT,"'") END END), '') )END, ' ',
UPPER(LTRIM(COALESCE(REPLACE(EXTRA, 'DEFAULT_GENERATED', ''), ''))) )

ELSE

CONCAT(' GENERATED ALWAYS AS (',

GENERATION_EXPRESSION, ') ', CASE EXTRA WHEN 'STORED GENERATED' THEN ' STORED '
WHEN 'VIRTUAL GENERATED' THEN ' VIRTUAL ' ELSE '' END)

END

ORDER BY C.ORDINAL_POSITION, '

' SEPARATOR ",") COL_DEF

,S.DATABASE_NAME
 ,S.TABLE_NAME

FROM DB_COMPARE S

LEFT JOIN `tables_backup`.INFORMATION_SCHEMA_COLUMNS

C ON S.DATABASE_NAME = C.TABLE_SCHEMA AND S.TABLE_NAME = C.TABLE_NAME AND
S.OBJECT_NAME = C.COLUMN_NAME

WHERE S.OBJECT_TYPE = 'Column' AND [Link] = 0 AND

S.DATABASE_NAME = @SOURCEDBNAME

GROUP BY S.DATABASE_NAME, S.TABLE_NAME

) C ON C.DATABASE_NAME = S.DATABASE_NAME AND C.TABLE_NAME =

S.TABLE_NAME

LEFT OUTER JOIN

SELECT I.DATABASE_NAME TABLE_SCHEMA

,I.TABLE_NAME

,GROUP_CONCAT(' DROP ', CASE CONSTRAINT_TYPE WHEN

'PRIMARY KEY' THEN 'PRIMARY KEY' WHEN 'UNIQUE' THEN 'CONSTRAINT ' ELSE ' KEY ' END,
OBJECT_NAME, '`, ',

' ADD ', CASE CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN

'PRIMARY' WHEN 'UNIQUE' THEN 'UNIQUE' ELSE '' END, CASE WHEN INDEX_TYPE = 'BTREE'
THEN ' KEY' ELSE CONCAT(' ', INDEX_TYPE, ' KEY ') END, '`', OBJECT_NAME, '` (',
INDEX_COLS, ' )' SEPARATOR "," ) INDEX_DEF

FROM DB_COMPARE I

LEFT OUTER JOIN

`tables_backup`.INFORMATION_SCHEMA_TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA =
I.DATABASE_NAME AND TC.TABLE_NAME = I.TABLE_NAME AND TC.CONSTRAINT_NAME =
I.OBJECT_NAME AND CONSTRAINT_TYPE NOT IN ('CHECK','FOREIGN KEY')

WHERE I.OBJECT_TYPE = 'Index' AND [Link] = 0 AND

I.DATABASE_NAME = @SOURCEDBNAME

GROUP BY I.DATABASE_NAME, I.TABLE_NAME

)I ON I.TABLE_SCHEMA = S.DATABASE_NAME AND I.TABLE_NAME =

S.TABLE_NAME

LEFT OUTER JOIN

SELECT TABLE_SCHEMA

,TABLE_NAME

,GROUP_CONCAT(CHECK_DEF SEPARATOR ",") CHECK_DEF

FROM (
 SELECT TC.TABLE_SCHEMA

,TC.TABLE_NAME

,GROUP_CONCAT(' DROP
CONSTRAINT `', TC.CONSTRAINT_NAME, '`, ',

' ADD CONSTRAINT `', TC.CONSTRAINT_NAME, '`

CHECK (', CHK.CHECK_CLAUSE, ')' SEPARATOR "," ) CHECK_DEF

FROM DB_COMPARE I

INNER JOIN
`tables_backup`.INFORMATION_SCHEMA_TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA =
I.DATABASE_NAME AND TC.TABLE_NAME = I.TABLE_NAME AND CONSTRAINT_TYPE = 'CHECK'

INNER JOIN
`tables_backup`.INFORMATION_SCHEMA_CHECK_CONSTRAINTS CHK ON TC.TABLE_SCHEMA =
CHK.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = CHK.CONSTRAINT_NAME

WHERE OBJECT_TYPE = 'CHECK' AND

I.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 0

GROUP BY TC.TABLE_SCHEMA, TC.TABLE_NAME

)CHK

GROUP BY TABLE_SCHEMA, TABLE_NAME

)CHK ON CHK.TABLE_SCHEMA = S.DATABASE_NAME AND

CHK.TABLE_NAME = S.TABLE_NAME

LEFT OUTER JOIN

SELECT TABLE_SCHEMA

,TABLE_NAME

,GROUP_CONCAT(FKEY_DEF SEPARATOR "," ) FKEY_DEF

FROM (

SELECT TC.TABLE_SCHEMA

,TC.TABLE_NAME

,CONCAT(' DROP CONSTRAINT `',

FKEY.CONSTRAINT_NAME, '`, ',

' ADD CONSTRAINT `', FKEY.CONSTRAINT_NAME, '`

FOREIGN KEY (', GROUP_CONCAT( '`', FKEY.COLUMN_NAME , '`' ORDER BY
FKEY.ORDINAL_POSITION SEPARATOR ","), ') REFERENCES ', '`',
FKEY.REFERENCED_TABLE_NAME, '`(', GROUP_CONCAT( '`', FKEY.REFERENCED_COLUMN_NAME ,
'`' ORDER BY FKEY.ORDINAL_POSITION SEPARATOR ",") , ')'
 , CASE WHEN
MAX(RC.UPDATE_RULE) = 'NO ACTION' THEN '' ELSE COALESCE(CONCAT(' ON UPDATE ',
MAX(RC.UPDATE_RULE),'')) END

, CASE WHEN MAX(RC.DELETE_RULE) = 'NO

ACTION' THEN '' ELSE COALESCE(CONCAT(' ON DELETE ', MAX(RC.DELETE_RULE),'')) END )
FKEY_DEF

FROM DB_COMPARE I

INNER JOIN
`tables_backup`.INFORMATION_SCHEMA_TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA =
I.DATABASE_NAME AND TC.TABLE_NAME = I.TABLE_NAME AND CONSTRAINT_TYPE = 'FOREIGN
KEY'

INNER JOIN
`tables_backup`.INFORMATION_SCHEMA_KEY_COLUMN_USAGE FKEY ON TC.TABLE_SCHEMA =
FKEY.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = FKEY.CONSTRAINT_NAME

LEFT OUTER JOIN

`tables_backup`.INFORMATION_SCHEMA_REFERENTIAL_CONSTRAINTS RC ON
RC.CONSTRAINT_SCHEMA = TC.TABLE_SCHEMA AND RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME

WHERE OBJECT_TYPE = 'FOREIGN KEY' AND

I.DATABASE_NAME = @SOURCEDBNAME AND [Link] = 0

GROUP BY TC.TABLE_SCHEMA, TC.TABLE_NAME,

FKEY.CONSTRAINT_NAME, FKEY.REFERENCED_TABLE_NAME

) FKEY

GROUP BY TABLE_SCHEMA, TABLE_NAME

)FKEY ON FKEY.TABLE_SCHEMA = S.DATABASE_NAME AND

FKEY.TABLE_NAME = S.TABLE_NAME

GROUP BY S.DATABASE_NAME, S.TABLE_NAME

UNION ALL

-- Drop the index, connstraints and columnns which are existing onny in destination

SELECT

CONCAT(' ALTER TABLE `', @DESTINATIONDBNAME , '`.`',S.TABLE_NAME, '` ',

SUBSTRING(CONCAT(COALESCE(CONCAT(",",MAX(C.COL_DEF)),''),
COALESCE(CONCAT(",",MAX(I.INDEX_DEF)),''),COALESCE(CONCAT(",",MAX(CHK.CHECK_DEF)),
'')),2) ,';') TABLE_DEFINITION

FROM (

SELECT DISTINCT S.DATABASE_NAME, S.TABLE_NAME

FROM DB_COMPARE S

WHERE S.DATABASE_NAME = @DESTINATIONDBNAME AND [Link] = 1 AND

OBJECT_TYPE <> 'Table'

AND NOT EXISTS ( SELECT 1 FROM DB_COMPARE D WHERE

S.DATABASE_NAME = D.DATABASE_NAME AND S.TABLE_NAME = D.TABLE_NAME AND D.OBJECT_TYPE
= 'Table')

) S

LEFT OUTER JOIN (

SELECT GROUP_CONCAT( 'DROP COLUMN `', OBJECT_NAME,

'`' SEPARATOR ",") COL_DEF

,S.DATABASE_NAME

,S.TABLE_NAME

FROM DB_COMPARE S

WHERE S.OBJECT_TYPE = 'Column' AND [Link] = 1 AND

S.DATABASE_NAME = @DESTINATIONDBNAME

GROUP BY S.DATABASE_NAME, S.TABLE_NAME

) C ON C.DATABASE_NAME = S.DATABASE_NAME AND C.TABLE_NAME =

S.TABLE_NAME

LEFT OUTER JOIN

SELECT I.DATABASE_NAME TABLE_SCHEMA

,I.TABLE_NAME

,GROUP_CONCAT('DROP ', CASE CONSTRAINT_TYPE WHEN

'PRIMARY KEY' THEN 'PRIMARY KEY ' WHEN 'UNIQUE' THEN 'CONSTRAINT ' ELSE ' KEY '
END, CASE CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN '' ELSE
CONCAT('`',OBJECT_NAME, '`' ) END SEPARATOR "," ) INDEX_DEF

FROM DB_COMPARE I

LEFT OUTER JOIN

`tables_backup`.INFORMATION_SCHEMA_TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA =
I.DATABASE_NAME AND TC.TABLE_NAME = I.TABLE_NAME AND TC.CONSTRAINT_NAME =
I.OBJECT_NAME AND CONSTRAINT_TYPE NOT IN ('CHECK','FOREIGN KEY')

WHERE I.OBJECT_TYPE = 'Index' AND [Link] = 1 AND

I.DATABASE_NAME = @DESTINATIONDBNAME
 GROUP BY I.DATABASE_NAME, I.TABLE_NAME

)I ON I.TABLE_SCHEMA = S.DATABASE_NAME AND I.TABLE_NAME =

S.TABLE_NAME

LEFT OUTER JOIN

SELECT TABLE_SCHEMA

,TABLE_NAME

,GROUP_CONCAT(CHECK_DEF SEPARATOR ",") CHECK_DEF

FROM (

SELECT TC.TABLE_SCHEMA

,TC.TABLE_NAME

,GROUP_CONCAT('DROP
CONSTRAINT `', TC.CONSTRAINT_NAME, '`, ' SEPARATOR "," ) CHECK_DEF

FROM DB_COMPARE I

INNER JOIN
`tables_backup`.INFORMATION_SCHEMA_TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA =
I.DATABASE_NAME AND TC.TABLE_NAME = I.TABLE_NAME AND CONSTRAINT_TYPE IN ('CHECK',
'FOREIGN KEY')

WHERE OBJECT_TYPE = 'CHECK' AND

I.DATABASE_NAME = @DESTINATIONDBNAME AND [Link] = 1

GROUP BY TC.TABLE_SCHEMA, TC.TABLE_NAME

)CHK

GROUP BY TABLE_SCHEMA, TABLE_NAME

)CHK ON CHK.TABLE_SCHEMA = S.DATABASE_NAME AND

CHK.TABLE_NAME = S.TABLE_NAME

GROUP BY S.DATABASE_NAME, S.TABLE_NAME

END //

DELIMITER ;
DROP PROCEDURE IF EXISTS SP_DBSCHEMACOMPARE_COPY_SYSTEM_DATA;

DELIMITER //

CREATE PROCEDURE SP_DBSCHEMACOMPARE_COPY_SYSTEM_DATA (SOURCEDBNAME VARCHAR(64))

BEGIN

SET @SOURCEDBNAME = SOURCEDBNAME COLLATE utf8_tolower_ci;

IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =

'information_schema_tables' LIMIT 1) THEN

INSERT INTO `tables_backup`.`information_schema_tables` SELECT * FROM

information_schema_tables WHERE TABLE_SCHEMA = @SOURCEDBNAME;

DELETE FROM `tables_backup`.`information_schema_tables` WHERE TABLE_SCHEMA

= @SOURCEDBNAME;

ELSE

CREATE TABLE `tables_backup`.`information_schema_tables` AS SELECT *

FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @SOURCEDBNAME;

ALTER TABLE `tables_backup`.`information_schema_tables` ADD KEY `ix_tables`

(TABLE_SCHEMA, TABLE_NAME);

END IF;

IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =

'information_schema_columns' LIMIT 1) THEN

DELETE FROM `tables_backup`.`information_schema_columns` WHERE

TABLE_SCHEMA = @SOURCEDBNAME;

INSERT INTO `tables_backup`.`information_schema_columns` SELECT * FROM

INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @SOURCEDBNAME;

ELSE

CREATE TABLE `tables_backup`.`information_schema_columns` AS SELECT *

FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @SOURCEDBNAME;

ALTER TABLE `tables_backup`.`information_schema_columns` ADD KEY

`ix_columns` (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME);

END IF;
 IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =
'information_schema_statistics' LIMIT 1) THEN

DELETE FROM `tables_backup`.`information_schema_statistics` WHERE

TABLE_SCHEMA = @SOURCEDBNAME;

INSERT INTO `tables_backup`.`information_schema_statistics` SELECT *

FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = @SOURCEDBNAME;

ELSE

CREATE TABLE `tables_backup`.`information_schema_statistics` AS SELECT

* FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = @SOURCEDBNAME;

ALTER TABLE `tables_backup`.`information_schema_statistics` ADD KEY

`ix_statistics` (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME);

END IF;

IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =

'information_schema_table_constraints' LIMIT 1) THEN

DELETE FROM `tables_backup`.`information_schema_table_constraints`

WHERE TABLE_SCHEMA = @SOURCEDBNAME;

INSERT INTO `tables_backup`.`information_schema_table_constraints`

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA =
@SOURCEDBNAME;

ELSE

CREATE TABLE `tables_backup`.`information_schema_table_constraints` AS

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA =
@SOURCEDBNAME;

ALTER TABLE `tables_backup`.`information_schema_table_constraints` ADD KEY

`ix_constraints` (TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME);

END IF;

IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =

'information_schema_check_constraints' LIMIT 1) THEN

DELETE FROM `tables_backup`.`information_schema_check_constraints`

WHERE CONSTRAINT_SCHEMA = @SOURCEDBNAME;

INSERT INTO `tables_backup`.`information_schema_check_constraints`

SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS WHERE CONSTRAINT_SCHEMA =
@SOURCEDBNAME;

ELSE
 CREATE TABLE `tables_backup`.`information_schema_check_constraints` AS
SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS WHERE CONSTRAINT_SCHEMA =
@SOURCEDBNAME;

ALTER TABLE `tables_backup`.`information_schema_check_constraints` ADD KEY

`ix_check_constraints` (CONSTRAINT_SCHEMA, CONSTRAINT_NAME);

END IF;

IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =

'information_schema_key_column_usage' LIMIT 1) THEN

DELETE FROM `tables_backup`.`information_schema_key_column_usage` WHERE

TABLE_SCHEMA = @SOURCEDBNAME;

INSERT INTO `tables_backup`.`information_schema_key_column_usage`

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA =
@SOURCEDBNAME;

ELSE

CREATE TABLE `tables_backup`.`information_schema_key_column_usage` AS

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA =
@SOURCEDBNAME;

ALTER TABLE `tables_backup`.`information_schema_key_column_usage` ADD KEY

`ix_key_column_usage` (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME);

END IF;

IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =

'information_schema_referential_constraints' LIMIT 1) THEN

DELETE FROM
`tables_backup`.`information_schema_referential_constraints` WHERE
CONSTRAINT_SCHEMA = @SOURCEDBNAME;

INSERT INTO
`tables_backup`.`information_schema_referential_constraints` SELECT * FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA =
@SOURCEDBNAME;

ELSE

CREATE TABLE
`tables_backup`.`information_schema_referential_constraints` AS SELECT * FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA =
@SOURCEDBNAME;

ALTER TABLE `tables_backup`.`information_schema_referential_constraints`

ADD KEY `ix_fkey_constraints` (CONSTRAINT_SCHEMA, CONSTRAINT_NAME);

END IF;
END //

DELIMITER ;

DROP PROCEDURE IF EXISTS SP_DBSCHEMACOMPARE_INSERT_SYSTEM_DATA;

DELIMITER //

CREATE PROCEDURE SP_DBSCHEMACOMPARE_INSERT_SYSTEM_DATA (SOURCEDBNAME VARCHAR(64))

BEGIN

DELETE FROM `tables_backup`.`information_schema_tables` WHERE

TABLE_SCHEMA collate utf8_unicode_ci = SOURCEDBNAME;

INSERT INTO `tables_backup`.`information_schema_tables` SELECT * FROM

INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA collate utf8_unicode_ci =
SOURCEDBNAME;

DELETE FROM `tables_backup`.`information_schema_columns` WHERE

TABLE_SCHEMA = SOURCEDBNAME;

INSERT INTO `tables_backup`.`information_schema_columns` SELECT * FROM

INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = SOURCEDBNAME;

DELETE FROM `tables_backup`.`information_schema_statistics` WHERE

TABLE_SCHEMA = SOURCEDBNAME;

INSERT INTO `tables_backup`.`information_schema_statistics` SELECT *

FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = SOURCEDBNAME;

DELETE FROM `tables_backup`.`information_schema_constraints` WHERE

TABLE_SCHEMA = SOURCEDBNAME;

INSERT INTO `tables_backup`.`information_schema_constraints` SELECT *

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = SOURCEDBNAME;

DELETE FROM `tables_backup`.`information_schema_check_constraints`

WHERE CONSTRAINT_SCHEMA = SOURCEDBNAME;
 INSERT INTO `tables_backup`.`information_schema_check_constraints`
SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS WHERE CONSTRAINT_SCHEMA =
SOURCEDBNAME;

DELETE FROM `tables_backup`.`information_schema_key_column_usage` WHERE

TABLE_SCHEMA = SOURCEDBNAME;

INSERT INTO `tables_backup`.`information_schema_key_column_usage`

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA =
SOURCEDBNAME;

DELETE FROM
`tables_backup`.`information_schema_referential_constraints` WHERE
CONSTRAINT_SCHEMA = SOURCEDBNAME;

INSERT INTO
`tables_backup`.`information_schema_referential_constraints` SELECT * FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = SOURCEDBNAME;

END //

DELIMITER ;