.::: Create Store Procedure Sync Manual Table, Add Column, Alter Column Compare Database (teguhth,aisyah) in MariaDB MarinaDB MySQL :::.

 

correlation https://teguhth.blogspot.com/2026/04/sync-manual-table-add-column-alter_10.html

1. CREATE TABLE (yang belum ada di aisyah)

DELIMITER $$

CREATE PROCEDURE sp_compare_table(
    IN p_source_db VARCHAR(100),
    IN p_target_db VARCHAR(100)
)
BEGIN
    SET SESSION group_concat_max_len = 1000000;

    SET @sql = CONCAT(
    'SELECT 
        CONCAT(
            ''CREATE TABLE ', p_target_db, '.'' , t.table_name, '' ( \n'',
            GROUP_CONCAT(
                CONCAT(
                    ''  '', c.column_name, '' '',
                    c.column_type,
                    IF(c.is_nullable = ''YES'', '' NULL'', '' NOT NULL'')
                )
                ORDER BY c.ordinal_position
                SEPARATOR '',\n''
            ),
            ''\n);''
        ) AS create_table_script
    FROM information_schema.tables t
    JOIN information_schema.columns c 
        ON t.table_schema = c.table_schema
        AND t.table_name = c.table_name
    WHERE t.table_schema = ''', p_source_db, '''
    AND t.table_name NOT IN (
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = ''', p_target_db, '''
    )
    GROUP BY t.table_name'
    );

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$

DELIMITER ;
 CALL sp_compare_add_column_table('teguhth', 'aisyah');

2. ADD COLUMN (kolom belum ada)

DELIMITER $$

CREATE PROCEDURE sp_compare_add_column_table(
    IN p_source_db VARCHAR(100),
    IN p_target_db VARCHAR(100)
)
BEGIN
    SET @sql = CONCAT(
    'SELECT 
        CONCAT(
            ''ALTER TABLE ', p_target_db, '.'' , c.table_name,
            '' ADD COLUMN '', c.column_name, '' '',
            c.column_type,
            '' NULL;''
        ) AS alter_script
    FROM information_schema.columns c
    JOIN information_schema.tables t 
        ON c.table_schema = t.table_schema 
        AND c.table_name = t.table_name
    LEFT JOIN information_schema.columns c2 
        ON c.table_name = c2.table_name
        AND c.column_name = c2.column_name
        AND c2.table_schema = ''', p_target_db, '''
    WHERE c.table_schema = ''', p_source_db, '''
    AND t.table_type = ''BASE TABLE''
    AND EXISTS (
        SELECT 1
        FROM information_schema.tables t2
        WHERE t2.table_schema = ''', p_target_db, '''
        AND t2.table_name = c.table_name
    )
    AND c2.column_name IS NULL'
    );

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$

DELIMITER ;

CALL sp_compare_add_column_table('teguhth', 'aisyah');

3. ALTER COLUMN (beda datatype / length / nullability)

DELIMITER $$

CREATE PROCEDURE sp_compare_alter_column_table(
    IN p_source_db VARCHAR(100),
    IN p_target_db VARCHAR(100)
)
BEGIN
    SET @sql = CONCAT(
    'SELECT 
        CONCAT(
            ''ALTER TABLE ', p_target_db, '.'' , c1.table_name,
            '' MODIFY COLUMN '', c1.column_name, '' '',
            c1.column_type,
            IF(c1.is_nullable = ''YES'', '' NULL'', '' NOT NULL''),
            IF(c1.column_default IS NOT NULL, 
                CONCAT('' DEFAULT '''''', c1.column_default, ''''''''), 
                ''''
            ),
            IF(c1.extra LIKE ''%auto_increment%'','' AUTO_INCREMENT'',''''),
            '';''
        ) AS alter_script
    FROM information_schema.columns c1
    JOIN information_schema.columns c2
        ON c1.table_name = c2.table_name
        AND c1.column_name = c2.column_name
    JOIN information_schema.tables t1
        ON c1.table_schema = t1.table_schema
        AND c1.table_name = t1.table_name
    JOIN information_schema.tables t2
        ON c2.table_schema = t2.table_schema
        AND c2.table_name = t2.table_name
    WHERE c1.table_schema = ''', p_source_db, '''
    AND c2.table_schema = ''', p_target_db, '''
    AND t1.table_type = ''BASE TABLE''
    AND t2.table_type = ''BASE TABLE''
    AND (
        c1.column_type <> c2.column_type
        OR c1.is_nullable <> c2.is_nullable
        OR IFNULL(c1.column_default,'''') <> IFNULL(c2.column_default,'''')
    )'
    );

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$

DELIMITER ;


CALL sp_compare_alter_column_table('teguhth', 'aisyah');