代码之家 › 专栏 › 技术社区 › CalvinDale

将SYSTEM\u TIME列从DATETIME2(7)缩小到DATETIME2(3)的脚本

temporal-tables ms-access sql-server

0

CalvinDale · 技术社区 · 7 年前

对于数据库中的所有时态表,我寻找一个生成T-SQL的脚本,以将其SYSTEM\u TIME列从DATETIME2(7)缩小到DATETIME2(3)。

我的理由: 当这些表通过ODBC连接进行链接时,我希望能够从MS Access数据库中更新这些表。Access不支持 DATETIME2(7) 用于通过ODBC驱动程序链接时的更新(字段太大)。相反地 表值参数 驱动程序工作正常(据推测,我还没试过);但它不支持 Active Directory-集成 连接。此外,我的应用程序不需要这些字段的额外精度。

生成的代码如下所示。只是好奇有没有人会有这样的剧本?

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 ...

2 回复 | 直到 7 年前

1

CalvinDale 7 年前

以下代码实现了这一点:

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

2

0

CalvinDale 7 年前

可以添加新列,将其设置为旧列的值,删除旧列,然后将新列重命名为旧名称。

下面将为您生成脚本,然后您可以执行这些脚本。我要提醒您,我前面没有SQL Server,因此您需要再次检查我的报价转义。

SELECT 'ALTER TABLE ' + t.table_name + ' ADD new_col DateTime2(3); UPDATE TABLE ' + 
        t.table_name + ' SET new_col = ' + c.column_name + '; ALTER TABLE ' + 
        t.table_name + ' DROP COLUMN ' + c.column_name + '; EXEC sp_rename ''' + 
        t.table_name + '.new_col'', ''' + c.column_name + ''' + , '''COLUMN''' + '; '
FROM information_schema.tables t 
    INNER JOIN information_schema.columns c ON t.object_id=c.object_id
WHERE c. --pick out your temporal columns here by type or name

ALTER TABLE myTable ADD new_col DateTime2(3); 
UPDATE myTable SET new_col = oldColName; 
ALTER TABLE myTable DROP COLUMN oldColName; 
EXEC sp_rename 'myTable.new_col', 'oldColName', 'COLUMN';