.::: Sync Manual Table, Add Column, Alter Column Compare Database (teguhth,aisyah) in SQL Server :::.

 

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

1. CREATE TABLE (yang belum ada di aisyah)

SELECT 
'CREATE TABLE aisyah.dbo.[' + t1.name + '] (' + CHAR(10) +
STRING_AGG(
    '    [' + c1.name + '] ' + 
    ty1.name +
    CASE 
        WHEN ty1.name IN ('varchar','char','varbinary','binary') 
            THEN '(' + 
                CASE WHEN c1.max_length = -1 THEN 'MAX' 
                     ELSE CAST(c1.max_length AS VARCHAR(10)) END + ')'
         WHEN ty1.name IN ('nvarchar','nchar') 
            THEN '(' + 
                CASE WHEN c1.max_length = -1 THEN 'MAX' 
                     ELSE CAST(c1.max_length/2 AS VARCHAR(10)) END + ')'

        WHEN ty1.name IN ('decimal','numeric') 
            THEN '(' + 
                CAST(c1.precision AS VARCHAR(10)) + ',' + 
                CAST(c1.scale AS VARCHAR(10)) + ')'
        ELSE ''
    END +
    CASE WHEN c1.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END
, ',' + CHAR(10))
+ CHAR(10) + ');'
FROM teguhth.sys.tables t1
JOIN teguhth.sys.columns c1 
    ON t1.object_id = c1.object_id
JOIN teguhth.sys.types ty1 
    ON c1.user_type_id = ty1.user_type_id
WHERE NOT EXISTS (
    SELECT 1 
    FROM aisyah.sys.tables t2 
    WHERE t2.name = t1.name
)
GROUP BY t1.name;
 

2. ADD COLUMN (kolom belum ada)

SELECT 
'ALTER TABLE aisyah.dbo.[' + t1.name + '] ADD [' + c1.name + '] ' +
CASE 
    WHEN ty1.name IN ('varchar','char','varbinary','binary') 
        THEN ty1.name + '(' + 
            CASE WHEN c1.max_length = -1 THEN 'MAX' 
                 ELSE CAST(c1.max_length AS VARCHAR(10)) END + ')'
    WHEN ty1.name IN ('nvarchar','nchar') 
        THEN ty1.name + '(' + 
            CASE WHEN c1.max_length = -1 THEN 'MAX' 
                 ELSE CAST(c1.max_length/2 AS VARCHAR(10)) END + ')'
    WHEN ty1.name IN ('decimal','numeric') 
        THEN ty1.name + '(' + 
            CAST(c1.precision AS VARCHAR(10)) + ',' + 
            CAST(c1.scale AS VARCHAR(10)) + ')'
    ELSE ty1.name
END
+ ' NULL;'   -- aman dulu
FROM teguhth.sys.tables t1
JOIN teguhth.sys.columns c1 
    ON t1.object_id = c1.object_id
JOIN teguhth.sys.types ty1 
    ON c1.user_type_id = ty1.user_type_id
LEFT JOIN aisyah.sys.tables t2 
    ON t1.name = t2.name
LEFT JOIN aisyah.sys.columns c2 
    ON t2.object_id = c2.object_id 
    AND c1.name = c2.name
WHERE 
    t2.name IS NOT NULL
    AND c2.name IS NULL;
 

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

SELECT 
'ALTER TABLE aisyah.dbo.[' + t1.name + '] ALTER COLUMN [' + c1.name + '] ' +
CASE 
    WHEN ty1.name IN ('varchar','char','varbinary','binary') 
        THEN ty1.name + '(' + 
            CASE WHEN c1.max_length = -1 THEN 'MAX' 
                 ELSE CAST(c1.max_length AS VARCHAR(10)) END + ')'
    WHEN ty1.name IN ('nvarchar','nchar') 
        THEN ty1.name + '(' + 
            CASE WHEN c1.max_length = -1 THEN 'MAX' 
                 ELSE CAST(c1.max_length/2 AS VARCHAR(10)) END + ')'
    WHEN ty1.name IN ('decimal','numeric') 
        THEN ty1.name + '(' + 
            CAST(c1.precision AS VARCHAR(10)) + ',' + 
            CAST(c1.scale AS VARCHAR(10)) + ')'
    ELSE ty1.name
END
+ CASE 
    WHEN c1.is_nullable = 1 THEN ' NULL;' 
    ELSE ' NOT NULL;' 
END
FROM teguhth.sys.tables t1
JOIN teguhth.sys.columns c1 
    ON t1.object_id = c1.object_id
JOIN teguhth.sys.types ty1 
    ON c1.user_type_id = ty1.user_type_id
JOIN aisyah.sys.tables t2 
    ON t1.name = t2.name
JOIN aisyah.sys.columns c2 
    ON t2.object_id = c2.object_id 
    AND c1.name = c2.name
JOIN aisyah.sys.types ty2 
    ON c2.user_type_id = ty2.user_type_id
WHERE 
    -- beda tipe / length / null
    (
        ty1.name <> ty2.name
        OR c1.max_length <> c2.max_length
        OR c1.is_nullable <> c2.is_nullable
        OR c1.precision <> c2.precision
        OR c1.scale <> c2.scale
    );