Bases de datos

MySQL

Diferencias entre bases de datos

Última actualización: 15-11-2017 06:52

Índice

Notas

Como programador en múltiples ocasiones me he visto obligado a confirmar que en la base de datos en PRODUCCIÓN del cliente se tienen iguales tablas, vistas, procedimientos, disparadores/triggers, etc. etc. comparando contra la base de datos de PRUEBAS y/o de DESARROLLO.

Los comandos SQL para comparar tablas, vistas y llaves foráneas ya los tenía en el "almacén" de comandos útiles de MySQL que tengo.

He desarrollado un procedimiento almacenado que me permite sacar listados de diferencias entre bases de datos indicando los nombres de estas y un parámetro que indica que tipo de diferencias son las que quiero ver.

Respuestas posibles del procedimiento aquí publicado:

Importante: Agradeceré mucho a cualquiera que me informe de alguna diferencia que no se detecte o si se encuentran con algún problema en el uso de este procedimiento. (Contacto)

El procedimiento MySQL


DROP PROCEDURE IF EXISTS sp_getDifferencesBetweenDBs;
 
DELIMITER $$
create procedure sp_getDifferencesBetweenDBs(IN opt varchar(10), IN db varchar(100), IN db2 varchar(100))
	COMMENT 'Listados de diferencias de objetos entre bases de datos'
begin
	declare vFilter varchar(20);
 
    SET GLOBAL group_concat_max_len = 4096;
 
    set vFilter = 'BASE TABLE';
	if (opt = 'V' or opt = 'VC') then 
		set vFilter = 'VIEW'; 
	end if;
 
    -- Tablas / vistas no existentes en alguna de la base de datos
    -- Tablas / vistas con ENGINE distinta
    if (opt = 'T' or opt = 'V') then         
		select a.TABLE_NAME, 
			b.ENGINE as DB_OBJECT_ENGINE, 
            c.ENGINE as DB2_OBJECT_ENGINE,
			case when b.TABLE_NAME is null then '' else 'X' end as IN_DB,
			case when c.TABLE_NAME is null then '' else 'X' end as IN_DB2,
			case when not ifnull(b.ENGINE, '') = ifnull(c.ENGINE, '') then 'X' else '' end as ENGINE_NOT_EQUAL
		from (
				select TABLE_NAME from information_schema.tables where table_schema = db and TABLE_TYPE = vFilter
				union select TABLE_NAME from information_schema.tables where table_schema = db2 and TABLE_TYPE = vFilter
				) a
				left outer join (select TABLE_NAME, ENGINE  from information_schema.tables where table_schema = db and TABLE_TYPE = vFilter) b
					on b.TABLE_NAME = a.TABLE_NAME
				left outer join (select TABLE_NAME, ENGINE from information_schema.tables where table_schema = db2 and TABLE_TYPE = vFilter) c
					on c.TABLE_NAME = a.TABLE_NAME                
			where b.TABLE_NAME is null or c.TABLE_NAME is null
					or not ifnull(b.ENGINE, '') = ifnull(c.ENGINE, '');
	end if;
 
    -- Columnas por tabla/vista que no existen en alguna base de datos o cuya definición es distinta
    if (opt = 'TC' or opt = 'VC') then
		select a.TABLE_NAME, a.COLUMN_NAME,
			case when b.TABLE_NAME is null then '' else 'X' end as IN_DB,
			case when c.TABLE_NAME is null then '' else 'X' end as IN_DB2,
            case when 
				not b.TABLE_NAME is null and not c.TABLE_NAME is null
                and (b.ORDINAL_POSITION = c.ORDINAL_POSITION
						or not b.COLUMN_DEFAULT = c.COLUMN_DEFAULT
						or not b.IS_NULLABLE = c.IS_NULLABLE
						or not b.COLUMN_TYPE = c.COLUMN_TYPE
						or not b.COLUMN_KEY = c.COLUMN_KEY
						or not b.EXTRA = c.EXTRA
                        ) then 'X'
                        else ''
			end as DISTINCT_DEFINITION ,
 
            concat(b.ORDINAL_POSITION, ' | ' , b.COLUMN_DEFAULT, ' | ' , b.IS_NULLABLE, ' | ' , b.COLUMN_TYPE, ' | ' , b.COLUMN_KEY, ' | ' , b.EXTRA) as DB_COL_INFO,
            concat(c.ORDINAL_POSITION, ' | ' , c.COLUMN_DEFAULT, ' | ' , c.IS_NULLABLE, ' | ' , c.COLUMN_TYPE, ' | ' , c.COLUMN_KEY, ' | ' , c.EXTRA) as DB2_COL_INFO
			-- b.ORDINAL_POSITION, b.COLUMN_DEFAULT, b.IS_NULLABLE, b.COLUMN_TYPE, b.COLUMN_KEY, b.EXTRA,
            -- c.ORDINAL_POSITION, c.COLUMN_DEFAULT, c.IS_NULLABLE, c.COLUMN_TYPE, c.COLUMN_KEY, c.EXTRA
 
		from (
				select TABLE_NAME, COLUMN_NAME from information_schema.columns where table_schema = db
				union select TABLE_NAME, COLUMN_NAME from information_schema.columns where table_schema = db2
				) a
                inner join (select TABLE_NAME from information_schema.tables where table_schema = db and TABLE_TYPE = vFilter) tdb on tdb.TABLE_NAME = a.TABLE_NAME
				inner join (select TABLE_NAME from information_schema.tables where table_schema = db2 and TABLE_TYPE = vFilter) tdb2 on tdb2.TABLE_NAME = a.TABLE_NAME
				left outer join (select TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, ifnull(COLUMN_DEFAULT, '') as COLUMN_DEFAULT, IS_NULLABLE, COLUMN_TYPE, COLUMN_KEY, EXTRA from information_schema.columns  where table_schema = db) b
					on b.TABLE_NAME = a.TABLE_NAME and b.COLUMN_NAME = a.COLUMN_NAME
				left outer join (select TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, ifnull(COLUMN_DEFAULT, '') as COLUMN_DEFAULT, IS_NULLABLE, COLUMN_TYPE, COLUMN_KEY, EXTRA from information_schema.columns  where table_schema = db2) c
					on c.TABLE_NAME = a.TABLE_NAME and c.COLUMN_NAME = a.COLUMN_NAME            
		where b.TABLE_NAME is null 
				or c.TABLE_NAME is null
                 or not b.ORDINAL_POSITION = c.ORDINAL_POSITION
				 or not b.COLUMN_DEFAULT = c.COLUMN_DEFAULT
				 or not b.IS_NULLABLE = c.IS_NULLABLE
				 or not b.COLUMN_TYPE = c.COLUMN_TYPE
				 or not b.COLUMN_KEY = c.COLUMN_KEY
				 or not b.EXTRA = c.EXTRA;    
    end if;
 
    -- Funciones/procedimientos que no existen en alguna base de datos o cuya definición es distinta
    if (opt = 'R') then
		select a.ROUTINE_NAME, 
			case when b.ROUTINE_NAME is null then '' else 'X' end as IN_DB,
			case when c.ROUTINE_NAME is null then '' else 'X' end as IN_DB2,
			case 
				when b.ROUTINE_NAME is null or c.ROUTINE_NAME is null then ''
				when not b.ROUTINE_TYPE = c.ROUTINE_TYPE then 'X'
				else ''
			end as DISTINCT_ROUTINE_TYPE,
			case 
				when b.ROUTINE_NAME is null or c.ROUTINE_NAME is null then ''
				when not b.DTD_IDENTIFIER = c.DTD_IDENTIFIER then 'X'
				else ''
			end as DISTINCT_DTD_IDENTIFIER,
			case 
				when b.ROUTINE_NAME is null or c.ROUTINE_NAME is null then ''
				when not b.ROUTINE_DEFINITION = c.ROUTINE_DEFINITION then 'X'
				else ''
			end as DISTINCT_ROUTINE_DEFINITION
		from
				(	select ROUTINE_NAME from information_schema.routines where ROUTINE_SCHEMA = db
					union select ROUTINE_NAME from information_schema.routines where ROUTINE_SCHEMA = db2
				) a
				left outer join (select ROUTINE_NAME, ROUTINE_TYPE, ifnull(DTD_IDENTIFIER, '') as DTD_IDENTIFIER, ROUTINE_DEFINITION from information_schema.routines where ROUTINE_SCHEMA = db) b
					on b.ROUTINE_NAME = a.ROUTINE_NAME 
				left outer join (select ROUTINE_NAME, ROUTINE_TYPE, ifnull(DTD_IDENTIFIER, '') as DTD_IDENTIFIER, ROUTINE_DEFINITION from information_schema.routines where ROUTINE_SCHEMA = db2) c
					on c.ROUTINE_NAME = a.ROUTINE_NAME
			where b.ROUTINE_NAME is null or c.ROUTINE_NAME is null 
					or (
							not (b.ROUTINE_NAME is null or c.ROUTINE_NAME is null )
							and (
									not b.ROUTINE_TYPE = c.ROUTINE_TYPE
									or not b.DTD_IDENTIFIER = c.DTD_IDENTIFIER 				
									or not b.ROUTINE_DEFINITION = c.ROUTINE_DEFINITION 				
								)
                            )
                    ;    
    end if;
 
    -- Parámetros por función/procedimiento que no existan en alguna base de datos o cuya definición es distinta
    if (opt = 'RP') then
		select a.ROUTINE_NAME, ifnull(d.PARAMETERS, 0) as DB_N_PARAMS, ifnull(e.PARAMETERS, 0) as DB2_N_PARAMS,
			params.PARAMETER_NAME,
			case when not pdb.ORDINAL_POSITION is null then 'X' else '' end IN_DB,
			case when not pdb2.ORDINAL_POSITION is null then 'X' else '' end IN_DB2,			
			case when
					not pdb.ORDINAL_POSITION is null and not pdb2.ORDINAL_POSITION is null
							and (
									not pdb2.ORDINAL_POSITION = pdb.ORDINAL_POSITION
									or not ifnull(pdb2.PARAMETER_MODE, '') = ifnull(pdb.PARAMETER_MODE, '')
									or not ifnull(pdb2.DTD_IDENTIFIER, '') = ifnull(pdb.DTD_IDENTIFIER, '')
									or not ifnull(pdb2.COLLATION_NAME, '') = ifnull(pdb.COLLATION_NAME, '')
									or not ifnull(pdb2.CHARACTER_SET_NAME, '') = ifnull(pdb.CHARACTER_SET_NAME, '')
								) then 'X'
					else ''
			end as DISTINCT_DEFINITION,			
			pdb.ORDINAL_POSITION as DB_ORDINAL_POSITION,     
			case when pdb.ORDINAL_POSITION = 0 then '[ ANSWER ]' else pdb.PARAMETER_NAME end as DB_PARAMETER_NAME, 
			pdb.PARAMETER_MODE as DB_PARAMETER_MODE, 
			pdb.DTD_IDENTIFIER as DB_DTD_IDENTIFIER, 
			pdb.COLLATION_NAME as DB_COLLATION_NAME, 
			pdb.CHARACTER_SET_NAME as DB_CHARACTER_SET_NAME,			
			pdb2.ORDINAL_POSITION as DB2_ORDINAL_POSITION,     
			case when pdb2.ORDINAL_POSITION = 0 then '[ ANSWER ]' else pdb2.PARAMETER_NAME end as DB2_PARAMETER_NAME, 
			pdb2.PARAMETER_MODE as DB2_PARAMETER_MODE, 
			pdb2.DTD_IDENTIFIER as DB2_DTD_IDENTIFIER, 
			pdb2.COLLATION_NAME as DB2_COLLATION_NAME, 
			pdb2.CHARACTER_SET_NAME as DB2_CHARACTER_SET_NAME						
		from
				(	select ROUTINE_NAME from information_schema.routines where ROUTINE_SCHEMA = db
					union select ROUTINE_NAME from information_schema.routines where ROUTINE_SCHEMA = db2 
				) a
				inner join (select ROUTINE_NAME, ROUTINE_TYPE, ifnull(DTD_IDENTIFIER, '') as DTD_IDENTIFIER, ROUTINE_DEFINITION from information_schema.routines where ROUTINE_SCHEMA = db) b
					on b.ROUTINE_NAME = a.ROUTINE_NAME 
				inner join (select ROUTINE_NAME, ROUTINE_TYPE, ifnull(DTD_IDENTIFIER, '') as DTD_IDENTIFIER, ROUTINE_DEFINITION from information_schema.routines where ROUTINE_SCHEMA = db2) c
					on c.ROUTINE_NAME = a.ROUTINE_NAME
				left outer join (select SPECIFIC_NAME as ROUTINE_NAME, count(*) as PARAMETERS from information_schema.PARAMETERS where SPECIFIC_SCHEMA = db group by SPECIFIC_NAME) d on d.ROUTINE_NAME = a.ROUTINE_NAME
				left outer join (select SPECIFIC_NAME as ROUTINE_NAME, count(*) as PARAMETERS from information_schema.PARAMETERS where SPECIFIC_SCHEMA = db2 group by SPECIFIC_NAME) e on e.ROUTINE_NAME = a.ROUTINE_NAME
				left outer join (
									select SPECIFIC_NAME, case when ORDINAL_POSITION = 0 then '[ ANSWER ]' else PARAMETER_NAME end as PARAMETER_NAME from information_schema.PARAMETERS where SPECIFIC_SCHEMA = db
									union select SPECIFIC_NAME, case when ORDINAL_POSITION = 0 then '[ ANSWER ]' else PARAMETER_NAME end as PARAMETER_NAME  from information_schema.PARAMETERS where SPECIFIC_SCHEMA = db2
								) params on params.SPECIFIC_NAME = a.ROUTINE_NAME
				left outer join information_schema.PARAMETERS pdb 
						on pdb.SPECIFIC_SCHEMA = db  
								and pdb.SPECIFIC_NAME = a.ROUTINE_NAME
								and case when pdb.ORDINAL_POSITION = 0 then '[ ANSWER ]' else pdb.PARAMETER_NAME end = params.PARAMETER_NAME
				left outer join information_schema.PARAMETERS pdb2 
						on pdb2.SPECIFIC_SCHEMA = db2  
								and pdb2.SPECIFIC_NAME = a.ROUTINE_NAME
								and case when pdb2.ORDINAL_POSITION = 0 then '[ ANSWER ]' else pdb2.PARAMETER_NAME end = params.PARAMETER_NAME
			where  pdb.ORDINAL_POSITION is null or pdb2.ORDINAL_POSITION is null
					or (
							not pdb.ORDINAL_POSITION is null and not pdb2.ORDINAL_POSITION is null
							and (
									not pdb2.ORDINAL_POSITION = pdb.ORDINAL_POSITION
									or not ifnull(pdb2.PARAMETER_MODE, '') = ifnull(pdb.PARAMETER_MODE, '')
									or not ifnull(pdb2.DTD_IDENTIFIER, '') = ifnull(pdb.DTD_IDENTIFIER, '')
									or not ifnull(pdb2.COLLATION_NAME, '') = ifnull(pdb.COLLATION_NAME, '')
									or not ifnull(pdb2.CHARACTER_SET_NAME, '') = ifnull(pdb.CHARACTER_SET_NAME, '')
								)
					);
    end if;
 
    -- Triggers/disparadores que no existen en alguna base de datos o cuya definición es distinta
    if (opt = 'TR') then		
		select a.TABLE_NAME, a.TRIGGER_NAME,
				case when not b.TABLE_NAME is null then 'X' else '' end as IN_DB,
				case when not c.TABLE_NAME is null then 'X' else '' end as IN_DB2,
				case 
					when b.TABLE_NAME is null or c.TABLE_NAME is null then ''
					when not ifnull(b.ACTION_STATEMENT, '') = ifnull(c.ACTION_STATEMENT, '') then 'X'
					else ''
				end as DISTINCT_ACTION_STATEMENT,
				case 
					when b.TABLE_NAME is null or c.TABLE_NAME is null then ''
					when not ifnull(b.EVENT_MANIPULATION, '') = ifnull(c.EVENT_MANIPULATION, '')
								 or not ifnull(b.EVENT_OBJECT_CATALOG, '') = ifnull(c.EVENT_OBJECT_CATALOG, '')
								 or not ifnull(b.ACTION_ORDER, '') = ifnull(c.ACTION_ORDER, '')
								 or not ifnull(b.ACTION_CONDITION, '') = ifnull(c.ACTION_CONDITION, '')						 
								 or not ifnull(b.ACTION_ORIENTATION, '') = ifnull(c.ACTION_ORIENTATION, '')
								 or not ifnull(b.ACTION_TIMING, '') = ifnull(c.ACTION_TIMING, '')
								 or not ifnull(b.DATABASE_COLLATION, '') = ifnull(c.DATABASE_COLLATION, '')
								 or not ifnull(b.COLLATION_CONNECTION, '') = ifnull(c.COLLATION_CONNECTION, '')
								 or not ifnull(b.CHARACTER_SET_CLIENT, '') = ifnull(c.CHARACTER_SET_CLIENT, '')
						then 'X'
						else ''
				end as DISTINCT_OTHER_DEFINITION,        
				b.EVENT_MANIPULATION as DB_EVENT_MANIPULATION, 
				b.EVENT_OBJECT_CATALOG as DB_EVENT_OBJECT_CATALOG, 
				b.ACTION_ORDER as DB_ACTION_ORDER, 
				b.ACTION_CONDITION as DB_ACTION_CONDITION, 
				b.ACTION_STATEMENT as DB_ACTION_STATEMENT, 
				b.ACTION_ORIENTATION as DB_ACTION_ORIENTATION, 
				b.ACTION_TIMING as DB_ACTION_TIMING, 
				b.DATABASE_COLLATION as DB_DATABASE_COLLATION, 
				b.COLLATION_CONNECTION as DB_COLLATION_CONNECTION, 
				b.CHARACTER_SET_CLIENT as DB_CHARACTER_SET_CLIENT, 
				c.EVENT_MANIPULATION as DB2_EVENT_MANIPULATION, 
				c.EVENT_OBJECT_CATALOG as DB2_EVENT_OBJECT_CATALOG, 
				c.ACTION_ORDER as DB2_ACTION_ORDER, 
				c.ACTION_CONDITION as DB2_ACTION_CONDITION, 
				c.ACTION_STATEMENT as DB2_ACTION_STATEMENT, 
				c.ACTION_ORIENTATION as DB2_ACTION_ORIENTATION, 
				c.ACTION_TIMING as DB2_ACTION_TIMING, 
				c.DATABASE_COLLATION as DB2_DATABASE_COLLATION, 
				c.COLLATION_CONNECTION as DB2_COLLATION_CONNECTION, 
				c.CHARACTER_SET_CLIENT as DB2_CHARACTER_SET_CLIENT
		from (
				select EVENT_OBJECT_TABLE as TABLE_NAME, TRIGGER_NAME from information_schema.TRIGGERS where TRIGGER_SCHEMA = db
				union select EVENT_OBJECT_TABLE as TABLE_NAME, TRIGGER_NAME from information_schema.TRIGGERS where TRIGGER_SCHEMA = db2 
				) a
				left outer join (
									select EVENT_OBJECT_TABLE as TABLE_NAME, TRIGGER_NAME,
											EVENT_MANIPULATION, EVENT_OBJECT_CATALOG, 
											ACTION_ORDER, ACTION_CONDITION,
											ACTION_STATEMENT, ACTION_ORIENTATION, ACTION_TIMING, 
											DATABASE_COLLATION, COLLATION_CONNECTION, CHARACTER_SET_CLIENT
									from information_schema.TRIGGERS 
									where TRIGGER_SCHEMA = db
								) b on b.TABLE_NAME = a.TABLE_NAME and b.TRIGGER_NAME = a.TRIGGER_NAME
				left outer join (
									select EVENT_OBJECT_TABLE as TABLE_NAME, TRIGGER_NAME,
											EVENT_MANIPULATION, EVENT_OBJECT_CATALOG, 
											ACTION_ORDER, ACTION_CONDITION,
											ACTION_STATEMENT, ACTION_ORIENTATION, ACTION_TIMING, 
											DATABASE_COLLATION, COLLATION_CONNECTION, CHARACTER_SET_CLIENT 
									from information_schema.TRIGGERS 
									where TRIGGER_SCHEMA = db2
								) c on c.TABLE_NAME = a.TABLE_NAME and c.TRIGGER_NAME = a.TRIGGER_NAME
			where b.TABLE_NAME is null 
					or c.TABLE_NAME is null
					or (not b.TABLE_NAME is null and  not c.TABLE_NAME is null
						 and (
								 not ifnull(b.EVENT_MANIPULATION, '') = ifnull(c.EVENT_MANIPULATION, '')
								 or not ifnull(b.EVENT_OBJECT_CATALOG, '') = ifnull(c.EVENT_OBJECT_CATALOG, '')
								 or not ifnull(b.ACTION_ORDER, '') = ifnull(c.ACTION_ORDER, '')
								 or not ifnull(b.ACTION_CONDITION, '') = ifnull(c.ACTION_CONDITION, '')
								 or not ifnull(b.ACTION_STATEMENT, '') = ifnull(c.ACTION_STATEMENT, '')
								 or not ifnull(b.ACTION_ORIENTATION, '') = ifnull(c.ACTION_ORIENTATION, '')
								 or not ifnull(b.ACTION_TIMING, '') = ifnull(c.ACTION_TIMING, '')
								 or not ifnull(b.DATABASE_COLLATION, '') = ifnull(c.DATABASE_COLLATION, '')
								 or not ifnull(b.COLLATION_CONNECTION, '') = ifnull(c.COLLATION_CONNECTION, '')
								 or not ifnull(b.CHARACTER_SET_CLIENT, '') = ifnull(c.CHARACTER_SET_CLIENT, '')
							)
					)
 
		;   
    end if;
 
    -- Restricciones/constraints no existentes en alguna de las bases de datos o con definición distinta
    if (opt = 'CONS') then
		select a.TABLE_NAME, a.CONSTRAINT_NAME,
				case when not b.TABLE_NAME is null then 'X' else '' end as IN_DB,
				case when not c.TABLE_NAME is null then 'X' else '' end as IN_DB2,
				case 
					when b.TABLE_NAME is null or c.TABLE_NAME is null then ''
					when not b.CONSTRAINT_TYPE = c.CONSTRAINT_TYPE then 'X'
					else ''
				end as DISTINCT_CONSTRAINT_TYPE,
				case 
					when b.TABLE_NAME is null or c.TABLE_NAME is null then ''
					when not b.CONSTRAINT_TYPE = c.CONSTRAINT_TYPE then ''
					when not b.CONSTRAINT_TYPE = 'FOREIGN KEY' then ''
					when not d.XREF = e.XREF then 'X'
					else ''
				end as DISTINCT_FK_DEFINITION,
				d.XREF as DB_FK_DEF,
				e.XREF as DB2_FK_DEF
 
		from (
				select TABLE_NAME, CONSTRAINT_NAME from information_schema.TABLE_CONSTRAINTS where table_schema = db
				union select TABLE_NAME, CONSTRAINT_NAME from information_schema.TABLE_CONSTRAINTS where table_schema = db2
				) a
				left outer join (select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE from information_schema.TABLE_CONSTRAINTS where table_schema = db) b
					on b.TABLE_NAME = a.TABLE_NAME and b.CONSTRAINT_NAME = a.CONSTRAINT_NAME
				left outer join (select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE from information_schema.TABLE_CONSTRAINTS where table_schema = db2) c
					on c.TABLE_NAME = a.TABLE_NAME and c.CONSTRAINT_NAME = a.CONSTRAINT_NAME
				left outer join (
									select b.TABLE_NAME, b.CONSTRAINT_NAME, 
										GROUP_CONCAT(
														concat(a.TABLE_NAME, '.' , a.COLUMN_NAME, ' => ', a.REFERENCED_TABLE_NAME, '.', a.REFERENCED_COLUMN_NAME) 
														order by a.ORDINAL_POSITION SEPARATOR ','
														)
														as XREF
 
									from information_schema.KEY_COLUMN_USAGE a 
											inner join information_schema.TABLE_CONSTRAINTS b
												on b.table_schema = db and a.constraint_schema = db
													and b.TABLE_NAME = a.TABLE_NAME
														and b.CONSTRAINT_NAME = a.CONSTRAINT_NAME
														and b.CONSTRAINT_TYPE = 'FOREIGN KEY'
									group by b.TABLE_NAME, b.CONSTRAINT_NAME        
								) d on d.TABLE_NAME = a.TABLE_NAME and d.CONSTRAINT_NAME = a.CONSTRAINT_NAME
				left outer join (
									select b.TABLE_NAME, b.CONSTRAINT_NAME, 
										GROUP_CONCAT(
														concat(a.TABLE_NAME, '.' , a.COLUMN_NAME, ' => ', a.REFERENCED_TABLE_NAME, '.', a.REFERENCED_COLUMN_NAME) 
														order by a.ORDINAL_POSITION SEPARATOR ','
														)
														as XREF
 
									from information_schema.KEY_COLUMN_USAGE a 
											inner join information_schema.TABLE_CONSTRAINTS b
												on b.table_schema = db2 and a.constraint_schema = db2
													and b.TABLE_NAME = a.TABLE_NAME
														and b.CONSTRAINT_NAME = a.CONSTRAINT_NAME
														and b.CONSTRAINT_TYPE = 'FOREIGN KEY'
									group by b.TABLE_NAME, b.CONSTRAINT_NAME        
								) e on e.TABLE_NAME = a.TABLE_NAME and e.CONSTRAINT_NAME = a.CONSTRAINT_NAME
		where b.TABLE_NAME is null or c.TABLE_NAME is null
		or (		not b.TABLE_NAME is null and not c.TABLE_NAME is null 
					and not b.CONSTRAINT_TYPE = c.CONSTRAINT_TYPE 
			);    
    end if;
 
end$$
DELIMITER ;

Cómo usar el procedimiento


-- Tablas no existentes en alguna de la base de datos o con ENGINE distinta
call sp_getDifferencesBetweenDBs('T', 'DB_ONE', 'DB_TWO');
 
-- Vistas no existentes en alguna de la base de datos o con ENGINE distinta
call sp_getDifferencesBetweenDBs('V', 'DB_ONE', 'DB_TWO');
 
-- Columnas por tabla que no existen en alguna base de datos o cuya definición es distinta
call sp_getDifferencesBetweenDBs('TC', 'DB_ONE', 'DB_TWO');
 
-- Columnas por vista que no existen en alguna base de datos o cuya definición es distinta
call sp_getDifferencesBetweenDBs('VC', 'DB_ONE', 'DB_TWO');
 
-- Funciones/procedimientos que no existen en alguna base de datos o cuya definición es distinta
call sp_getDifferencesBetweenDBs('R', 'DB_ONE', 'DB_TWO');
 
-- Parámetros por función/procedimiento que no existan en alguna base de datos o cuya definición es distinta
call sp_getDifferencesBetweenDBs('RP', 'DB_ONE', 'DB_TWO');
 
-- Triggers/disparadores que no existen en alguna base de datos o cuya definición es distinta
call sp_getDifferencesBetweenDBs('TR', 'DB_ONE', 'DB_TWO');
 
-- Restricciones/constraints no existentes en alguna de las bases de datos o con definición distinta
call sp_getDifferencesBetweenDBs('CONS', 'DB_ONE', 'DB_TWO');
 

Notas acerca de que hay en cada una de las respuestas posibles

A continuación podrán ver comentarios sobre el resultado de cada una de las opciones que se pueden utilizar en el procedimiento.

Opción de consulta Parámetro a usar Columna del resultado Comentario
Tablas / Vistas T,V TABLE_NAMENombre de la tabla
DB_OBJECT_ENGINEENGINE/MOTOR de la tabla base de datos I
DB2_OBJECT_ENGINEENGINE/MOTOR de la tabla base de datos II
IN_DBExiste en base de datos I
IN_DB2Existe en base de datos II
ENGINE_NOT_EQUALBandera 0/1 ENGINE/MOTOR desigual
Columnas x tabla/vista TC, VC TABLE_NAMENombre de la tabla
COLUMN_NAMENombre de columna
IN_DBExiste en base de datos I
IN_DB2Existe en base de datos II
DISTINCT_DEFINITIONDefinición de columna distinta
DB_COL_INFOConcatenación de posición ordinal, valor por omisión, si acepta nulos, tipo de columna, COLUMN_KEY y EXTRA
DB2_COL_INFOConcatenación de posición ordinal, valor por omisión, si acepta nulos, tipo de columna, COLUMN_KEY y EXTRA
Rutinas :: Procedimientos/Funciones R ROUTINE_NAMENombre de función/procedimiento
IN_DBExiste en base de datos I
IN_DB2Existe en base de datos II
DISTINCT_ROUTINE_TYPEBandera 0/1 si es distinto el tipo de rutina
DISTINCT_DTD_IDENTIFIERBandera 0/1 si es distinto el tipo de respuesta
DISTINCT_ROUTINE_DEFINITIONBandera 0/1 si es distinta la definición de la rutina
Parámetros por rutina RP ROUTINE_NAMENombre de función/procedimiento
DB_N_PARAMSCantidad de parámetros en base de datos I
DB2_N_PARAMSCantidad de parámetros en base de datos II
PARAMETER_NAMENombre de parámetro
IN_DBExiste en base de datos I
IN_DB2Existe en base de datos II
DISTINCT_DEFINITIONDefinición de parámetro distinta
DB_ORDINAL_POSITIONPosición ordinal del parámetro en base de datos I
DB_PARAMETER_NAMENombre del parámetro en base de datos I
DB_PARAMETER_MODEModo del parámetro en base de datos I
DB_DTD_IDENTIFIERTipo de respuesta en base de datos I
DB_COLLATION_NAMEColación en base de datos I
DB_CHARACTER_SET_NAMEConjunto de carácteres en base de datos I
DB2_ORDINAL_POSITIONPosición ordinal del parámetro en base de datos II
DB2_PARAMETER_NAMENombre del parámetro en base de datos II
DB2_PARAMETER_MODEModo del parámetro en base de datos II
DB2_DTD_IDENTIFIERTipo de respuesta en base de datos II
DB2_COLLATION_NAMEColación en base de datos II
DB2_CHARACTER_SET_NAMEConjunto de carácteres en base de datos II
Disparadores / triggers TR TABLE_NAMENombre de la tabla
TRIGGER_NAMENombre del disparador/trigger
IN_DBExiste en base de datos I
IN_DB2Existe en base de datos II
DISTINCT_ACTION_STATEMENTBandera 0/1 de definición distinta
DISTINCT_OTHER_DEFINITIONBandera 0/1 de "otra definición" distinta
DB_EVENT_MANIPULATIONManipulación de evento en base de datos I
DB_EVENT_OBJECT_CATALOGCatálogo de objeto de evento en base de datos I
DB_ACTION_ORDEROrden de acción en base de datos I
DB_ACTION_CONDITIONCondición de acción en base de datos I
DB_ACTION_STATEMENTDefinición de acción en base de datos I
DB_ACTION_ORIENTATIONOrientación de acción en base de datos I
DB_ACTION_TIMINGTiempo de acción en base de datos I
DB_DATABASE_COLLATIONColación de conexión en base de datos I
DB_COLLATION_CONNECTIONColación de conexión en base de datos I
DB_CHARACTER_SET_CLIENTJuego de carácteres del cliente en base de datos I
DB2_EVENT_MANIPULATIONManipulación de evento en base de datos II
DB2_EVENT_OBJECT_CATALOGCatálogo de objeto de evento en base de datos II
DB2_ACTION_ORDEROrden de acción en base de datos II
DB2_ACTION_CONDITIONCondición de acción en base de datos II
DB2_ACTION_STATEMENTDefinición de acción en base de datos II
DB2_ACTION_ORIENTATIONOrientación de acción en base de datos II
DB2_ACTION_TIMINGTiempo de acción en base de datos II
DB2_DATABASE_COLLATIONColación de conexión en base de datos II
DB2_COLLATION_CONNECTIONColación de conexión en base de datos II
DB2_CHARACTER_SET_CLIENTJuego de carácteres del cliente en base de datos II
Restricciones CONS TABLE_NAMENombre de la tabla
CONSTRAINT_NAMENombre de restricción
IN_DBExiste en base de datos I
IN_DB2Existe en base de datos II
DISTINCT_CONSTRAINT_TYPEBandera de tipo de restricción distinto
DISTINCT_FK_DEFINITIONBandera de definición distinta de llave foránea
DB_FK_DEFDefinición de llave foránea en base de datos I
DB2_FK_DEFDefinición de llave foránea en base de datos II