MySQL query to show difference between development and production schema

Question

I would like to have a query using the schema database in MySQL
that shows the difference between the columns, triggers and stored procedures between two database schema's: production and development.

Query, not tools
I've seen Compare two MySQL databases
Which lists the tools that can perform this task, but I would like to know is if there is a query that can perform this task.
Please only suggest queries, I really do not want to know about tools, command line hacks or such.

I am looking to see if the production database and development database are out of sync.
And which fields, procedures etc where added or changed, so I can update the production database if I roll out a new update of the client software that uses the database.

I'm using MySQL 5.1 latest version.

Answer 1

Johan, try to run this script. Specify two databases you want to compare in variables at the begining of the script. Query returns data-set, and sets statuses for table/view columns.

Status 'Only in source' - object exists only in db1; Status 'Only in target' - object exists only in db2; Status 'In both schemas' - object exists in db1 and in db2, but details can be different; for example: value 'varchar(255)/int(11)' says that source field type is 'varchar(255)' and target is 'int(11)', value 'null' says that details are equal;

SET @source_db = 'db1';
SET @target_db = 'db2';

SELECT 
  'Only in source' exist_type,
  c1.table_schema, c1.table_name, c1.column_name, c1.ordinal_position, c1.column_default, c1.is_nullable, c1.numeric_precision, c1.numeric_scale, c1.character_set_name, c1.collation_name, c1.column_type, c1.column_key, c1.extra, c1.column_comment
FROM
  (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @source_db) c1
  LEFT JOIN (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @target_db) c2
    ON c1.TABLE_name = c2.TABLE_name AND c1.column_name = c2.column_name
WHERE c2.column_name is null

UNION ALL

SELECT
  'Only in target' exist_type,
  c2.table_schema, c2.table_name, c2.column_name, c2.ordinal_position, c2.column_default, c2.is_nullable, c2.numeric_precision, c2.numeric_scale, c2.character_set_name, c2.collation_name, c2.column_type, c2.column_key, c2.extra, c2.column_comment
FROM
  (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @source_db) c1
  RIGHT JOIN (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @target_db) c2
    ON c1.TABLE_name = c2.TABLE_name AND c1.column_name = c2.column_name
WHERE c1.column_name is null

UNION ALL

SELECT 
  'In both schemas' exist_type,
  CONCAT(c1.table_schema, '/', c2.table_schema),
  c1.table_name, c1.column_name,
  IF(c1.ordinal_position = c2.ordinal_position OR c1.ordinal_position IS NULL AND c2.ordinal_position IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.ordinal_position, ''), IFNULL(c2.ordinal_position, ''))),
  IF(c1.column_default = c2.column_default OR c1.column_default IS NULL AND c2.column_default IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.column_default, ''), IFNULL(c2.column_default, ''))),
  IF(c1.is_nullable = c2.is_nullable OR c1.is_nullable IS NULL AND c2.is_nullable IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.is_nullable, ''), IFNULL(c2.is_nullable, ''))),
  IF(c1.numeric_precision = c2.numeric_precision OR c1.numeric_precision IS NULL AND c2.numeric_precision IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.numeric_precision, ''), IFNULL(c2.numeric_precision, ''))),
  IF(c1.numeric_scale = c2.numeric_scale OR c1.numeric_scale IS NULL AND c2.numeric_scale IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.numeric_scale, ''), IFNULL(c2.numeric_scale, ''))),
  IF(c1.character_set_name = c2.character_set_name OR c1.character_set_name IS NULL AND c2.character_set_name IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.character_set_name, ''), IFNULL(c2.character_set_name, ''))),
  IF(c1.collation_name = c2.collation_name OR c1.collation_name IS NULL AND c2.collation_name IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.collation_name, ''), IFNULL(c2.collation_name, ''))),
  IF(c1.column_type = c2.column_type OR c1.column_type IS NULL AND c2.column_type IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.column_type, ''), IFNULL(c2.column_type, ''))),
  IF(c1.column_key = c2.column_key OR c1.column_key IS NULL AND c2.column_key IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.column_key, ''), IFNULL(c2.column_key, ''))),
  IF(c1.extra = c2.extra OR c1.extra IS NULL AND c2.extra IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.extra, ''), IFNULL(c2.extra, ''))),
  IF(c1.column_comment = c2.column_comment OR c1.column_comment IS NULL AND c2.column_comment IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.column_comment, ''), IFNULL(c2.column_comment, '')))
FROM
  (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @source_db) c1
  JOIN (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @target_db) c2
    ON c1.TABLE_name = c2.TABLE_name AND c1.column_name = c2.column_name;

This script can be modified to find differences between triggers and routines.