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

 

correlation https://teguhth.blogspot.com/2025/03/query-to-check-table-column-compare_28.html

1. CREATE TABLE (yang belum ada di aisyah)

SELECT 
CONCAT(
    'CREATE TABLE aisyah.', 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 = 'teguhth'
AND t.table_name NOT IN (
    SELECT table_name 
    FROM information_schema.tables 
    WHERE table_schema = 'aisyah'
)
GROUP BY t.table_name;
  

2. ADD COLUMN (kolom belum ada)

SELECT 
CONCAT(
    'ALTER TABLE aisyah.', 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 = 'aisyah'
WHERE c.table_schema = 'teguhth'
AND t.table_type = 'BASE TABLE'
AND EXISTS (
    SELECT 1
    FROM information_schema.tables t2
    WHERE t2.table_schema = 'aisyah'
    AND t2.table_name = c.table_name
)
AND c2.column_name IS NULL;
 

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

SELECT 
CONCAT(
    'ALTER TABLE aisyah.', c1.table_name,
    ' MODIFY COLUMN ', c1.column_name, ' ',
    c1.column_type,
    IF(c1.is_nullable = 'YES', ' NULL', ' NOT NULL'),
    ';'
) 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 = 'teguhth'
AND c2.table_schema = 'aisyah'
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,'')
);