MySQL Schema Compare

mysql_schema_compare.sql

SET @database_current = '<production>';
SET @database_dev = '<development>';
-- column and datatype comparison
SELECT a.TABLE_NAME, a.COLUMN_NAME, a.DATA_TYPE, a.CHARACTER_MAXIMUM_LENGTH,
	b.COLUMN_NAME, b.DATA_TYPE, b.CHARACTER_MAXIMUM_LENGTH
FROM information_schema.COLUMNS a
	LEFT JOIN information_schema.COLUMNS b ON b.COLUMN_NAME = a.COLUMN_NAME
		AND b.TABLE_NAME = a.TABLE_NAME
		AND b.TABLE_SCHEMA = @database_current
WHERE a.TABLE_SCHEMA = @database_dev
	AND (
		b.COLUMN_NAME IS NULL
        OR b.COLUMN_NAME != a.COLUMN_NAME
        OR b.DATA_TYPE != a.DATA_TYPE
        OR b.CHARACTER_MAXIMUM_LENGTH != a.CHARACTER_MAXIMUM_LENGTH
	);
-- table comparison    
SELECT a.TABLE_SCHEMA, a.TABLE_NAME, b.TABLE_NAME
FROM information_schema.TABLES a
	LEFT JOIN information_schema.TABLES b ON b.TABLE_NAME = a.TABLE_NAME
		AND b.TABLE_SCHEMA = @database_current
WHERE a.TABLE_SCHEMA = @database_dev
	AND (
		b.TABLE_NAME IS NULL
        OR b.TABLE_NAME != a.TABLE_NAME
	);