Create a view to Alter a Table and Add three Columns

ALTER TABLE FullyQualifiedTableName add [UserAuthorizationKey] [int] not null DEFAULT(-99)
ALTER TABLE FullyQualifiedTableName add [DateAdded] [datetime2](7)   not null DEFAULT(sysdatetime())
ALTER TABLE FullyQualifiedTableName add [DateOfLastUpdate] [datetime2](7)   not null DEFAULT(sysdatetime())

select concat(t.TABLE_SCHEMA,'.',t.TABLE_NAME) as FullyQualifiedTableName
     , t.TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES as t
where t.TABLE_SCHEMA in ('CH01-01-Dimension','CH01-01-Fact') and t.TABLE_TYPE = 'BASE TABLE'



CREATE VIEW [Utils].[uvw_AlterTableAddColumnsUserAuthorizationKeyAndTwoTimeStamps]
/*
ALTER TABLE FullyQualifiedTableName 
ADD [NewColumnName] [Datatype | User Defined Datatype] 
[not null | null] 
DEFAULT(Default Value for NewColumnName )
*/AS
SELECT t.TABLE_SCHEMA AS SchemaName,
       CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME) AS FullyQualifiedTableName,
       1 AS SortOrder,
       CONCAT(
                 'ALTER TABLE ',
                 t.TABLE_SCHEMA,
                 '.',
                 t.TABLE_NAME,
                 ' add [UserAuthorizationKey] [int] not null DEFAULT(-99)'
             ) AS AlterFullyQualifiedTableNameAddingColumn
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_TYPE = 'BASE TABLE'
UNION
SELECT t.TABLE_SCHEMA AS SchemaName,
       CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME) AS FullyQualifiedTableName,
       2 AS SortOrder,
       CONCAT(
                 'ALTER TABLE ',
                 t.TABLE_SCHEMA,
                 '.',
                 t.TABLE_NAME,
                 ' add [DateAdded] [datetime2](7) not null DEFAULT(sysdatetime())'
             ) AS AlterFullyQualifiedTableNameAddingColumn
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_TYPE = 'BASE TABLE'
UNION
SELECT t.TABLE_SCHEMA AS SchemaName,
       CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME) AS FullyQualifiedTableName,
       3 AS SortOrder,
       CONCAT(
                 'ALTER TABLE ',
                 t.TABLE_SCHEMA,
                 '.',
                 t.TABLE_NAME,
                 ' add [DateOfLastUpdate] [datetime2](7) not null DEFAULT(sysdatetime())'
             ) AS AlterFullyQualifiedTableNameAddingColumn
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_TYPE = 'BASE TABLE'
GO



SELECT   v.SchemaName
       , v.TableName
       , v.FullyQualifiedTableName
       , v.SortOrder
       , v.AlterFullyQualifiedTableNameAddingColumn
FROM            Utils.uvw_AlterTableAddColumnsUserAuthorizationKeyAndTwoTimeStamps AS v
ORDER BY FullyQualifiedTableName, SortOrder

----Sample before and after of the execution

------ Before Adding the three columns

----CREATE TABLE [dbo].[Digits](
----[digit] [INT] NOT NULL
----) ON [PRIMARY]
----GO

------ Add the three columns

----ALTER TABLE dbo.Digits add [UserAuthorizationKey] [int] not null DEFAULT(-99)
----ALTER TABLE dbo.Digits add [DateAdded] [datetime2](7) not null DEFAULT(sysdatetime())
----ALTER TABLE dbo.Digits add [DateOfLastUpdate] [datetime2](7) not null DEFAULT(sysdatetime())

------ After Adding the three columns

----CREATE TABLE [dbo].[Digits](
----[digit] [INT] NOT NULL,
----[UserAuthorizationKey] [INT] NOT NULL,
----[DateAdded] [DATETIME2](7) NOT NULL,
----[DateOfLastUpdate] [DATETIME2](7) NOT NULL
----) ON [PRIMARY]
----GO

----ALTER TABLE [dbo].[Digits] ADD  DEFAULT ((-99)) FOR [UserAuthorizationKey]
----GO

----ALTER TABLE [dbo].[Digits] ADD  DEFAULT (SYSDATETIME()) FOR [DateAdded]
----GO

----ALTER TABLE [dbo].[Digits] ADD  DEFAULT (SYSDATETIME()) FOR [DateOfLastUpdate]
----GO