#
#  requirements: MySQL 5
#  stored procedure: sp_compare_tables
#  parameter: source_table        - source table name
#  parameter: destination_table   - destination table name
#  description: display columns from source table which not exists in destination table
#

DELIMITER \\

DROP PROCEDURE IF EXISTS `sp_compare_tables`\\

CREATE PROCEDURE sp_compare_tables(IN source_table VARCHAR(100),IN destination_table VARCHAR(100))

BEGIN

    DECLARE sql_text VARCHAR(400);
    SET @sql_text = CONCAT("SELECT DISTINCT COLUMN_NAME,DATA_TYPE,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS as inf WHERE inf.TABLE_NAME = '",source_table,"' AND  inf.COLUMN_NAME NOT IN (SELECT destinf.COLUMN_NAME FROM information_schema.COLUMNS as destinf WHERE destinf.TABLE_NAME = '",destination_table,"')");

    PREPARE sql_stm FROM @sql_text;
    EXECUTE sql_stm;
    DEALLOCATE PREPARE sql_stm;

END\\

DELIMITER ;