POWERSHELL: Generates a script to add modifiedby, and modifieddate columns to tables

AddModifiedColumns.ps1

<#
.SYNOPSIS
    Generates a T-SQL script that adds 'ModifiedBy' and 'ModifiedDate' columns to all user tables in a SQL Server database that do not already have them.

.DESCRIPTION
    This script connects to the specified SQL Server and scans for user tables missing the 'ModifiedBy' and 'ModifiedDate' columns.
    It then generates a SQL script that:
      - Adds these columns with default values.
      - Creates AFTER UPDATE triggers to automatically populate these fields.

    Authentication is configurable (Windows or SQL authentication).
    Schema filtering is optional.
    Output is written to a .sql file (default: AddModifiedColumns.sql).

.PARAMETER ServerName
    The name or network address of the SQL Server instance.

.PARAMETER DatabaseName
    The target SQL Server database name.

.PARAMETER Schemas
    Optional array of schema names to filter tables by. Defaults to all schemas if unspecified (uses "*").

.PARAMETER OutputFile
    Optional path and filename to write the output SQL script to. Defaults to "AddModifiedColumns.sql".

.PARAMETER UseWindowsAuth
    Switch to use Windows Authentication. Enabled by default.

.PARAMETER Username
    SQL Server login username. Required only if UseWindowsAuth is false.

.PARAMETER Password
    SQL Server login password. Required only if UseWindowsAuth is false.

.EXAMPLE
    .\AddModifiedColumns.ps1 -ServerName "localhost" -DatabaseName "MyDatabase"

    Uses Windows Authentication to scan all schemas in "MyDatabase" and generate the SQL script.

.EXAMPLE
    .\AddModifiedColumns.ps1 -ServerName "localhost" -DatabaseName "MyDatabase" -Schemas "dbo","audit"

    Limits the processing to "dbo" and "audit" schemas.

.EXAMPLE
    .\AddModifiedColumns.ps1 -ServerName "localhost" -DatabaseName "MyDatabase" -UseWindowsAuth:$false -Username "sa" -Password "YourP@ssword"

    Uses SQL authentication to connect and generate the SQL script.

.NOTES
    Author: Tim Cartwright
    Created: 2024-06-24
    Dependencies: SqlServer or SQLPS PowerShell module
    Tested on: PowerShell 5.1, SQL Server 2019+
#>
param(
    [Parameter(Mandatory=$true)]
    [string]$ServerName,
    
    [Parameter(Mandatory=$true)]
    [string]$DatabaseName,
    
    [Parameter(Mandatory=$false)]
    [string[]]$Schemas = @("*"),

    [Parameter(Mandatory=$false)]
    [string]$OutputFile = "AddModifiedColumns.sql",
    
    [Parameter(Mandatory=$false)]
    [switch]$UseWindowsAuth = $true,
    
    [Parameter(Mandatory=$false)]
    [string]$Username,
    
    [Parameter(Mandatory=$false)]
    [string]$Password
)

# Import SQL Server module if available
try {
    Import-Module SqlServer -ErrorAction Stop
} catch {
    Write-Warning "SqlServer module not found. Attempting to use SQLPS..."
    try {
        Import-Module SQLPS -ErrorAction Stop
    } catch {
        Write-Error "Neither SqlServer nor SQLPS module found. Please install SQL Server PowerShell module."
        return
    }
}

# Build connection string
if ($UseWindowsAuth) {
    $connectionString = "Server=$ServerName;Database=$DatabaseName;Integrated Security=True;Encrypt=Optional;"
} else {
    if (-not $Username -or -not $Password) {
        Write-Error "Username and Password are required when not using Windows Authentication"
        return
    }
    $connectionString = "Server=$ServerName;Database=$DatabaseName;User Id=$Username;Password=$Password;Encrypt=Optional;"
}

# Query to get all user tables that don't already have the columns
$query = @"
SELECT 
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    TRIM(STUFF((
        SELECT CONCAT('AND [t].[', [c].[name], '] = [i].[', [c].[name], ']', CHAR(10), CHAR(9), CHAR(9))
        FROM sys.indexes i
        INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE i.object_id = t.object_id 
        AND i.is_primary_key = 1
        ORDER BY ic.key_ordinal
        FOR XML PATH('')
    ), 1, 4, '')) AS PrimaryKeyColumns
FROM sys.tables t
WHERE t.type = 'U'  -- User tables only
AND NOT EXISTS (
    SELECT 1 FROM sys.columns c 
    WHERE c.object_id = t.object_id 
    AND c.name IN ('ModifiedBy', 'ModifiedDate')
)
"@

if ($Schemas.Count -gt 0 -and -not ($Schemas | Where-Object { $_ -match "%|\*" } )) {
    $schemaList = ($Schemas | ForEach-Object { "'$_'" }) -join ','
    $query += "`nAND SCHEMA_NAME(t.schema_id) IN ($schemaList)"
}
$query += "`nORDER BY SCHEMA_NAME(t.schema_id), t.name"

try {
    Write-Host "Connecting to $ServerName.$DatabaseName..." -ForegroundColor Green
    
    # Execute query to get table list
    $tables = Invoke-Sqlcmd -ConnectionString $connectionString -Query $query
    
    if ($tables.Count -eq 0) {
        Write-Host "No tables found that need the ModifiedBy/ModifiedDate columns added." -ForegroundColor Yellow
        return
    }
    
    Write-Host "Found $($tables.Count) tables that need modification." -ForegroundColor Green
    
    # Generate T-SQL script
    $sqlScript = @"
-- =====================================================
-- Script to add ModifiedBy and ModifiedDate columns
-- Generated on: $(Get-Date)
-- Server: $ServerName
-- Database: $DatabaseName
-- Schemas: $(($tables.schemaname | Select-Object -Unique | Sort-Object) -join ", ")
-- =====================================================

USE [$DatabaseName]
GO

"@

    foreach ($table in $tables) {
        $schemaName = $table.SchemaName
        $tableName = $table.TableName
        $keyColumns = $table.PrimaryKeyColumns.Trim()
        $fullTableName = "[$schemaName].[$tableName]"
        
        $sqlScript += @"

-- Adding columns to $fullTableName
RAISERROR('Adding ModifiedBy and ModifiedDate columns to $fullTableName', 0, 1) WITH NOWAIT;
ALTER TABLE $fullTableName
ADD [ModifiedBy] VARCHAR(100) NULL CONSTRAINT [DF_$($tableName)_ModifiedBy] DEFAULT (ORIGINAL_LOGIN()),
    [ModifiedDate] DATETIMEOFFSET(7) NULL CONSTRAINT [DF_$($tableName)_ModifiedDate] DEFAULT (SYSDATETIMEOFFSET());

GO

CREATE TRIGGER [TR_$($tableName)_UpdateModified]
ON $fullTableName
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    UPDATE t
    SET ModifiedBy = ORIGINAL_LOGIN(),
        ModifiedDate = SYSDATETIMEOFFSET()
    FROM $fullTableName t
    INNER JOIN inserted i ON $($keyColumns);
END
GO

"@
    }
    
    # Add update trigger template (commented out)
    $sqlScript += @"
-- Script execution completed
RAISERROR('ModifiedBy and ModifiedDate columns have been added to all applicable tables.', 0, 1) WITH NOWAIT;
"@

    if (-not [System.IO.Path]::IsPathRooted($OutputFile)) {
        $OutputFile = [System.IO.Path]::Combine($env:TEMP, $OutputFile);
    }
    # Write to file
    $sqlScript | Out-File -FilePath $OutputFile -Encoding UTF8
    & notepad $OutputFile
    
    Write-Host "`nT-SQL script generated successfully!" -ForegroundColor Green
    Write-Host "Output file: $OutputFile" -ForegroundColor Cyan
    Write-Host "Tables processed: $($tables.Count)" -ForegroundColor Cyan
    
    # Display table list
    Write-Host "`nTables that will be modified:" -ForegroundColor Yellow
    foreach ($table in $tables) {
        Write-Host "  - $($table.SchemaName).$($table.TableName)" -ForegroundColor White
    }
    
    Write-Host "`nNext steps:" -ForegroundColor Yellow
    Write-Host "1. Review the generated SQL script: $OutputFile" -ForegroundColor White
    Write-Host "2. Test the script in a development environment first" -ForegroundColor White
    Write-Host "3. Execute the script in your target database" -ForegroundColor White
    Write-Host "4. Consider creating update triggers to automatically populate ModifiedBy/ModifiedDate" -ForegroundColor White

} catch {
    Write-Error "Error occurred: $($_.Exception.Message)"
    return
}