Back (Current repo: scraps)

compare_schema_struct.sql (3978B)

USE some_schema;

DROP PROCEDURE IF EXISTS `compare_schemas`;

DELIMITER $$

CREATE PROCEDURE compare_schemas(IN source_db VARCHAR(64), IN target_db VARCHAR(64))
BEGIN
    SELECT
      'Only in source' AS 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' AS 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' AS 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;
END $$

DELIMITER ;