SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [AddColumnTrigger]
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
    -- Check if the target table is Subproject
    IF EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)') = 'Subproject'
    BEGIN
        
        DECLARE @columnName NVARCHAR(128) -- the added column name
        SELECT TOP 1 @columnName = COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'Subproject'
            AND COLUMN_NAME NOT IN (
                SELECT REPLACE(name, '_Vis', '')
                FROM sys.columns
                WHERE object_id = OBJECT_ID('SubprojectVisibility')
            )
        ORDER BY ORDINAL_POSITION DESC
        -- Check if the column exists and is not present in SubprojectVisibility
        IF @columnName IS NOT NULL AND NOT EXISTS (
            SELECT 1
            FROM sys.columns
            WHERE object_id = OBJECT_ID('SubprojectVisibility')
                AND name = @columnName + '_Vis'
        )
        BEGIN
            DECLARE @sql NVARCHAR(MAX)
            SET @sql = N'
                ALTER TABLE SubprojectVisibility
                ADD ' + QUOTENAME(@columnName + '_Vis') + ' TINYINT NOT NULL DEFAULT (0);
            '
-- replace TINYINT with desired dataType and value
            EXEC sp_executesql @sql
        END
    END
END
GO
ENABLE TRIGGER [AddColumnTrigger] ON DATABASE
GO

ALTER TABLE dbo.Subproject
ADD NewExampleColumn int;

Subproject 
SubprojectId | SubprojectName | NewExampleColumn
SubprojectVisibility
SubprojectId_vis | SubprojectName_Vis |  NewExampleColumn_Vis