Skip to content

Instantly share code, notes, and snippets.

@gajoseph
Created May 22, 2018 19:01
Show Gist options
  • Save gajoseph/cab0404cf58fa9be5a86e702427843c3 to your computer and use it in GitHub Desktop.
Save gajoseph/cab0404cf58fa9be5a86e702427843c3 to your computer and use it in GitHub Desktop.
mysql compare tables in 2 schemas
DELIMITER $$;
DROP FUNCTION IF EXISTS `mysql`.`sf_compTablesInschemas`$$
CREATE DEFINER=`root`@`%` FUNCTION `sf_compTablesInschemas`( pvchTablename VARCHAR(100),pvchSchema1 varchar(100)
,pvchSchema2 varchar(100)
) RETURNS varchar(20) CHARSET latin1
DETERMINISTIC
BEGIN
declare asd int default 0;
select COUNT(*) From (
select * from (
select TABLE_NAME, COLUMN_NAME, COLUMN_TYPE from information_schema.columns where table_schema = pvchSchema1
and TABLE_NAME =pvchTablename
union all
select TABLE_NAME, COLUMN_NAME, COLUMN_TYPE from information_schema.columns where table_schema = pvchSchema2
and TABLE_NAME =pvchTablename) A
group by TABLE_NAME, COLUMN_NAME, COLUMN_TYPE having COUNT(*) =1 ) A1
into asd;
if asd > 0 then
return 'false';
else
return 'true';
end if ;
END$$
DELIMITER ;$$
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment