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.
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.