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

 

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


1. CREATE TABLE (yang belum ada di aisyah)

CREATE PROCEDURE sp_compare_table
    @source_db SYSNAME,
    @target_db SYSNAME
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);
     SET @sql = '
    SELECT 
    ''CREATE TABLE ' + @target_db + '.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 ' + @source_db + '.sys.tables t1
    JOIN ' + @source_db + '.sys.columns c1 
        ON t1.object_id = c1.object_id
    JOIN ' + @source_db + '.sys.types ty1 
        ON c1.user_type_id = ty1.user_type_id
    WHERE NOT EXISTS (
        SELECT 1 
        FROM ' + @target_db + '.sys.tables t2 
        WHERE t2.name = t1.name
    )
    GROUP BY t1.name;
    ';

    EXEC sp_executesql @sql;
END;


EXEC sp_compare_table 'teguhth', 'aisyah';
 

2. ADD COLUMN (kolom belum ada)

CREATE PROCEDURE sp_compare_add_column_table
    @source_db SYSNAME,
    @target_db SYSNAME
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);

    SET @sql = '
    SELECT 
    ''ALTER TABLE ' + @target_db + '.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;''
    FROM ' + @source_db + '.sys.tables t1
    JOIN ' + @source_db + '.sys.columns c1 
        ON t1.object_id = c1.object_id
    JOIN ' + @source_db + '.sys.types ty1 
        ON c1.user_type_id = ty1.user_type_id
    LEFT JOIN ' + @target_db + '.sys.tables t2 
        ON t1.name = t2.name
    LEFT JOIN ' + @target_db + '.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;
    ';

    EXEC sp_executesql @sql;
END;


EXEC sp_compare_add_column_table 'teguhth', 'aisyah';
 

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

CREATE PROCEDURE sp_compare_alter_column_table
    @source_db SYSNAME,
    @target_db SYSNAME
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);

    SET @sql = '
    SELECT 
    ''ALTER TABLE ' + @target_db + '.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 ' + @source_db + '.sys.tables t1
    JOIN ' + @source_db + '.sys.columns c1 
        ON t1.object_id = c1.object_id
    JOIN ' + @source_db + '.sys.types ty1 
        ON c1.user_type_id = ty1.user_type_id
    JOIN ' + @target_db + '.sys.tables t2 
        ON t1.name = t2.name
    JOIN ' + @target_db + '.sys.columns c2 
        ON t2.object_id = c2.object_id 
        AND c1.name = c2.name
    JOIN ' + @target_db + '.sys.types ty2 
        ON c2.user_type_id = ty2.user_type_id
    WHERE 
        (
            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
        );
    ';

    EXEC sp_executesql @sql;
END;


EXEC sp_compare_alter_column_table 'teguhth', 'aisyah';