As mentioned in the comments, this is an extremely bad database design.
But if you just want to generate a list of scripts to add the missing columns to the tables, something like this should work:
WITH cteTables (Name) As
(
SELECT N'[dbo].[Table1]'
UNION ALL SELECT N'[dbo].[Table2]'
UNION ALL SELECT N'[dbo].[Table3]'
),
cteColumns (Name, Definition) As
(
SELECT N'Column1', N'nvarchar(254) NULL'
UNION ALL SELECT N'Column2', N'nvarchar(254) NULL'
),
cteToCreate As
(
SELECT
T.Name As TableName,
C.Name As ColumnName,
C.Definition
FROM
cteTables As T
CROSS JOIN cteColumns As C
WHERE
Not Exists
(
SELECT 1
FROM sys.columns As E
WHERE E.object_id = OBJECT_ID(T.Name)
And E.name = C.Name
)
)
SELECT
N'ALTER TABLE ' + T.TableName + N' ADD '
+ STUFF(
(
SELECT N', ' + QUOTENAME(C.ColumnName) + N' ' + C.Definition
FROM cteToCreate As C
WHERE C.TableName = T.TableName
ORDER BY C.ColumnName
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)'),
1, 2, N'')
+ N';'
FROM
cteToCreate As T
GROUP BY
TableName
;
If you execute that, you'll get a list of commands you need to execute to create the missing columns. For example:
ALTER TABLE [dbo].[Table1] ADD [Column2] nvarchar(254) NULL;
ALTER TABLE [dbo].[Table3] ADD [Column1] nvarchar(254) NULL, [Column2] nvarchar(254) NULL;