Script to downsize SYSTEM_TIME column from DATETIME2(7) to DATETIME2(3)

Question

For all temporal tables within a database, I seek a script which generates T-SQL to downsize their SYSTEM_TIME columns from DATETIME2(7) to DATETIME2(3).

My rationale: I want to be able to update such tables from within an MS Access database when the tables are linked via an ODBC connection. Access doesn't support DATETIME2(7) for updates when linked via the ODBC driver (The field is too large). Conversely, the SQL Server Native Client driver works (supposedly, I haven't tried it); but it doesn't support Active Directory - Integrated connections. Moreover my application doesn't require the extra precision for these fields.

The generated code will be something like the following. Just curious if someone might have such a script?

CREATE TABLE [dbo].[Tasks] (
    [Id]           INT                                                IDENTITY (1, 1) NOT NULL,
    [Task]         NVARCHAR(50)                                       NOT NULL,
    [ValidFrom]    DATETIME2(7) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [ValidTo]      DATETIME2 (7) GENERATED ALWAYS AS ROW END HIDDEN   NOT NULL,
    [Who]          NVARCHAR (128)                                     CONSTRAINT [DF_dbo_Tasks_Who] DEFAULT (SUSER_SNAME()) NOT NULL,
    CONSTRAINT [PK_dbo_Tasks] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [UQ_Tasks_Task] UNIQUE NONCLUSTERED ([Task] ASC),
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[History_Tasks], DATA_CONSISTENCY_CHECK=ON));
GO

INSERT INTO dbo.Tasks (Task)
VALUES (N'Task 1'), (N'Task 2'), (N'Task 3'), (N'Task 4'), (N'Task 5');
GO

UPDATE dbo.Tasks SET Task = N'Task 1.1' WHERE Id = 1;
DELETE FROM dbo.Tasks WHERE Id = 3;
INSERT INTO dbo.Tasks (Task) VALUES (N'Task 6');
GO

SELECT * FROM dbo.Tasks 
SELECT * FROM dbo.History_Tasks
GO
---------------------------------------------------------------------------------------------------
ALTER TABLE dbo.Tasks SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.Tasks DROP PERIOD FOR SYSTEM_TIME;
GO
ALTER TABLE dbo.Tasks ALTER COLUMN ValidFrom DATETIME2(3) NOT NULL;
ALTER TABLE dbo.Tasks ALTER COLUMN ValidTo   DATETIME2(3) NOT NULL;
DROP INDEX ix_History_Tasks ON dbo.History_Tasks;
ALTER TABLE dbo.History_Tasks ALTER COLUMN ValidFrom DATETIME2(3) NOT NULL;
ALTER TABLE dbo.History_Tasks ALTER COLUMN ValidTo DATETIME2(3) NOT NULL;
ALTER TABLE dbo.Tasks ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
ALTER TABLE dbo.Tasks SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.History_Tasks));
CREATE CLUSTERED INDEX ix_History_Tasks ON dbo.History_Tasks(ValidTo ASC, ValidFrom ASC);
GO
---------------------------------------------------------------------------------------------------
-- ... Next table ...

Answer 1

The following code does the trick:

WITH a AS (
    SELECT
          SCHEMA_NAME(t.schema_id) AS PrimarySchema
        , t.name AS PrimaryTable
        , OBJECT_SCHEMA_NAME(t.history_table_id) AS HistorySchema
        , OBJECT_NAME(t.history_table_id) AS HistoryTable
        , i.name AS HistoryIndexName
    FROM sys.tables AS t
    JOIN sys.indexes AS i ON t.history_table_id = i.object_id -- There should only be one index on each history table
    WHERE t.temporal_type = 2 -- SYSTEM_VERSIONED_TEMPORAL_TABLE
), b AS (
    SELECT a.PrimarySchema
         , a.PrimaryTable
         , CONCAT(a.PrimarySchema, N'.', a.PrimaryTable) AS PrimarySchemaTable
         , a.HistorySchema
         , a.HistoryTable
         , CONCAT(a.HistorySchema, N'.', a.HistoryTable) AS HistorySchemaTable
         , a.HistoryIndexName
    FROM a
), c AS (
    SELECT b.PrimarySchema, b.PrimaryTable
         , CONCAT(N'ALTER TABLE ', b.PrimarySchemaTable , N' SET (SYSTEM_VERSIONING = OFF);')                                             AS L1
         , CONCAT(N'ALTER TABLE ', b.PrimarySchemaTable , N' DROP PERIOD FOR SYSTEM_TIME;')                                               AS L2
         , N'GO'                                                                                                                          AS L3
         , CONCAT(N'ALTER TABLE ', b.PrimarySchemaTable , N' ALTER COLUMN ValidFrom DATETIME2(3) NOT NULL;')                              AS L4
         , CONCAT(N'ALTER TABLE ', b.PrimarySchemaTable , N' ALTER COLUMN ValidTo   DATETIME2(3) NOT NULL;')                              AS L5
         , CONCAT(N'DROP INDEX ', b.HistoryIndexName, N' ON ', b.HistorySchemaTable , N';')                                               AS L6
         , CONCAT(N'ALTER TABLE ', b.HistorySchemaTable, N' ALTER COLUMN ValidFrom DATETIME2(3) NOT NULL;')                               AS L7
         , CONCAT(N'ALTER TABLE ', b.HistorySchemaTable, N' ALTER COLUMN ValidTo DATETIME2(3) NOT NULL;')                                 AS L8
         , CONCAT(N'ALTER TABLE ', b.PrimarySchemaTable , N' ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);')                           AS L9
         , CONCAT(N'ALTER TABLE ', b.PrimarySchemaTable , N' SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=', b.HistorySchemaTable, N'));')  AS L10
         , CONCAT(N'CREATE CLUSTERED INDEX ', b.HistoryIndexName, N' ON ', b.HistorySchemaTable, N'(ValidTo ASC, ValidFrom ASC);')        AS L11
         , N'GO'                                                                                                                          AS L12
         , N'-------------------------------------------------------------------------------------------------------------------------'   AS L13
    FROM b
)
SELECT CONCAT(c.L1, CHAR(10), c.L2, CHAR(10), c.L3, CHAR(10), c.L4, CHAR(10), c.L5, CHAR(10), c.L6, CHAR(10), c.L7, CHAR(10), c.L8, CHAR(10), c.L9, CHAR(10), c.L10, CHAR(10), c.L11, CHAR(10), c.L12, CHAR(10), c.L13, CHAR(10)) AS Statements
FROM c
ORDER BY c.PrimarySchema, c.PrimaryTable