POWERSHELL: Generates history or temporal tables

Generate-HistoryOrTemporalTables.ps1

<#
.SYNOPSIS
Generates T-SQL scripts to enable temporal or history tracking for all user tables in a SQL Server database.

.DESCRIPTION
This PowerShell script connects to a specified SQL Server and database, detects the SQL Server version,
and generates T-SQL to enable historical tracking of data changes.

- For SQL Server **2016 or later** (version 13+):
  - The script generates ALTER TABLE statements to enable **system-versioned temporal tables**.
  - It adds hidden `ValidFrom` and `ValidTo` columns, and links to a dedicated history table.
    - The columns are only hidden if **2019 or later** (version 15+):

- For SQL Server **versions earlier than 2016**:
  - The script generates T-SQL to create **manual history tables**.
  - It adds `AFTER INSERT, UPDATE, DELETE` triggers to log data changes into these tables.
  - History tables include `HistoryInsertedAt` and `ChangeType` columns.

.PARAMETER ServerName
The SQL Server instance name (e.g., "localhost", "MyServer\SQL2022").

.PARAMETER DatabaseName
The name of the target database to scan for user tables.

.PARAMETER Schemas
Optional. A list of schema names to limit processing to (e.g., @("dbo", "sales")).

.EXAMPLE
.\Generate-HistoryOrTemporalTables.ps1 -ServerName "MyServer" -DatabaseName "MyDb"

Scans all user tables in "MyDb" and outputs version-appropriate T-SQL scripts for history tracking.

.EXAMPLE
.\Generate-HistoryOrTemporalTables.ps1 -ServerName "MyServer" -DatabaseName "MyDb" -Schemas @("dbo")

Limits processing to tables within the "dbo" schema.

.NOTES
Author: Tim Cartwright  
Requires: PowerShell 5.1+ or PowerShell Core + SqlServer module  
SQL Server support: 2008 - 2022+  
Version: 1.0  
#>
 param (
    [Parameter(Mandatory = $true)]
    [string]$ServerName,

    [Parameter(Mandatory = $true)]
    [string]$DatabaseName,

    [Parameter()]
    [string[]]$Schemas = @(),

    [Parameter()]
    [switch]$ForceHistoryTables
)

Clear-Host

function Get-SqlVersion {
    param (
        [string]$ServerName
    )
    $query = "SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)"
    Invoke-Sqlcmd -ServerInstance $ServerName -Database "master" -Query $query -Encrypt Optional -ErrorAction Stop |
        Select-Object -ExpandProperty Column1
}

function Get-UserTables {
    param (
        [string]$ServerName,
        [string]$DatabaseName,
        [string[]]$Schemas
    )
    $schemaFilter = if ($Schemas.Count -gt 0) {
        "'" + ($Schemas -join "','") + "'"
    } else {
        "SCHEMA_NAME(t.schema_id)"
    }

    $query = @"
SELECT SCHEMA_NAME(t.schema_id) AS [SchemaName],
    t.name AS [TableName],
    OBJECTPROPERTY(t.[object_id], 'TableHasPrimaryKey') AS [TableHasPrimaryKey],
    STUFF((
        SELECT CONCAT(', [', c.name, ']')
        FROM sys.columns c 
        JOIN sys.[tables] AS [t2] ON [t2].object_id = c.object_id
        WHERE [t2].object_id = [t].[object_id]
            AND [t2].schema_id = [t].[schema_id]
        ORDER BY c.[column_id]
        FOR XML PATH('')
    ), 1, 2, '') AS [Columns]
FROM sys.tables t
WHERE is_ms_shipped = 0
  AND SCHEMA_NAME(t.schema_id) IN ($schemaFilter)
"@
    Invoke-Sqlcmd -ServerInstance $ServerName -Database $DatabaseName -Query $query -Encrypt Optional -ErrorAction Stop
}

function Generate-TemporalScript {
    param (
        [string]$SchemaName,
        [string]$TableName,
        [int]$SqlMajorVersion
    )
    $hidden = if ($SqlMajorVersion -ge 15) {
        "HIDDEN"
    } else { "" }

    return @"
-- Alter table [$SchemaName].[$TableName] to be system-versioned
ALTER TABLE [$SchemaName].[$TableName]
ADD
    ValidFrom DATETIMEOFFSET GENERATED ALWAYS AS ROW START $hidden NOT NULL DEFAULT SYSDATETIMEOFFSET(),
    ValidTo   DATETIMEOFFSET GENERATED ALWAYS AS ROW END   $hidden NOT NULL DEFAULT CONVERT(DATETIMEOFFSET, '9999-12-31 23:59:59.9999999'),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE [$SchemaName].[$TableName]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [$SchemaName].[${TableName}_History]));
"@
}

function Generate-TriggerScript {
    param (
        [string]$SchemaName,
        [string]$TableName,
        [string]$columns
    )

    # Query the primary key columns
    $pkQuery = @"
        SELECT c.name
        FROM sys.indexes i
        JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
        JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        JOIN sys.objects o ON o.object_id = i.object_id
        WHERE i.is_primary_key = 1
          AND OBJECT_NAME(i.object_id) = '$TableName'
          AND SCHEMA_NAME(o.schema_id) = '$SchemaName'
        ORDER BY ic.key_ordinal;
"@

    $pkColumns = Invoke-Sqlcmd -Query $pkQuery -ServerInstance $ServerName -Database $DatabaseName -Encrypt Optional | ForEach-Object { $_.name }

    $pkClause = ""
    if ($pkColumns.Count -gt 0) {
        $pkColList = $pkColumns -join ", "
        $pkClause = @"
    -- Add primary key to history table
    ALTER TABLE [$SchemaName].[${TableName}_History]
    ADD CONSTRAINT [PK_${TableName}_History] PRIMARY KEY CLUSTERED ($pkColList);
"@
    }


    return @"
-- Create manual history table for [$SchemaName].[$TableName]
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[$SchemaName].[${TableName}_History]') AND type = 'U')
BEGIN
    SELECT $($columns -replace "\[", "[t].["), 
           SYSDATETIMEOFFSET() AS HistoryDate,
           CAST('INSERT' AS VARCHAR(20)) AS HistoryChange
    INTO [$SchemaName].[${TableName}_History]
    FROM [$SchemaName].[$TableName] AS [t]
    WHERE 1 = 0;

$pkClause
END;

-- Add triggers to capture changes
GO
CREATE OR ALTER TRIGGER [${TableName}_History_Trigger]
ON [$SchemaName].[$TableName]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @action VARCHAR(20) = 'DELETE'

    IF EXISTS (SELECT * FROM inserted)
    BEGIN
        SET @action = 'INSERT'
        IF EXISTS (SELECT * FROM deleted) BEGIN
            SET @action = 'UPDATE'
        END 

        INSERT INTO [$SchemaName].[${TableName}_History]
            ($columns, [HistoryDate], [HistoryChange])
        SELECT 
            $($columns -replace "\[", "[i].["), SYSDATETIMEOFFSET(), @action
        FROM inserted AS [i];
    END ELSE IF EXISTS (SELECT * FROM deleted)
    BEGIN
        INSERT INTO [$SchemaName].[${TableName}_History]
            ($columns, [HistoryDate], [HistoryChange])
        SELECT 
            $($columns -replace "\[", "[d].["), SYSDATETIMEOFFSET(), @action
        FROM deleted AS [d];
    END
END;
GO
"@
}

# -------- Main Execution --------

$versionString = Get-SqlVersion -ServerName $ServerName
$majorVersion = [int]($versionString.Split('.')[0])

Write-Host "SQL Server version: $versionString (Major: $majorVersion)"

$tables = Get-UserTables -ServerName $ServerName -Database $DatabaseName -Schemas $Schemas

foreach ($table in $tables) {
    $schema = $table.SchemaName
    $name = $table.TableName

    Write-Host "`n-- Generating script for $schema.$name --" -ForegroundColor Cyan

    if ($majorVersion -le 13 -or $ForceHistoryTables.IsPresent) {
        $script = Generate-TriggerScript -SchemaName $schema -TableName $name -columns $table.Columns
    } elseif ($table.TableHasPrimaryKey -eq 1) {
        $script = Generate-TemporalScript -SchemaName $schema -TableName $name -SqlMajorVersion $majorVersion
    } else {
        $script  = "/****************************************************************************/"
        $script += "/* Skipping temporal table [$($schema)].[$($name)] as it has no primary key */"
        $script += "/****************************************************************************/"
    }

    Write-Output $script
}