Stored procedure for MS SQL Server to generate history tables and triggers.

GenerateAuditTableAndTrigger-MSSQL.sql

/************************************************************************************************************
Inspired by: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=84331
Created By:  Bryan Massey
Created On:  3/11/2007

Updated By:  Dimitri Vranken
Updated On:  21/01/2016

Explanation:
1) Queries system tables to retrieve table schema for @TableName parameter.
2) Creates a History table (@TableName + '_History') to mimic the original table, plus additional history columns.
3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table, which is used to populate the History table.
4) Writes simple script to pre-populate the Audit table with the current values of the Audited table.

Usage: 
EXEC [dbo].[GenerateHistoryTableAndTrigger] @TableName = N'tblBlah', @CreateTrigger = N'Y', @ExecuteProcedure = N'Y'
************************************************************************************************************/

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'GenerateHistoryTableAndTrigger' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
 EXEC ('DROP PROCEDURE dbo.GenerateHistoryTableAndTrigger')
GO

CREATE PROCEDURE dbo.GenerateHistoryTableAndTrigger
    @TableName VARCHAR(255),
    @CreateTrigger CHAR(1) = 'Y',
    @ExecuteProcedure CHAR(1) = 'N' 
AS


DECLARE @SQLTable VARCHAR(MAX), @SQLTrigger VARCHAR(MAX), @FieldList VARCHAR(6000), @FirstField VARCHAR(200), @HistoryTableName VARCHAR(MAX)
DECLARE @TAB CHAR(1), @CRLF CHAR(1), @SQL VARCHAR(1000), @Date VARCHAR(12)

SET @TAB = CHAR(9)
SET @CRLF = CHAR(13) + CHAR(10)
SET @Date = CONVERT(VARCHAR(12), GETDATE(), 101)
SET @FieldList = ''
SET @SQLTable = ''


DECLARE @FieldName VARCHAR(100), @DataType VARCHAR(50) 
DECLARE @FieldLength VARCHAR(10), @Precision VARCHAR(10), @Scale VARCHAR(10),  @FieldDescr VARCHAR(500), @AllowNulls VARCHAR(1)

DECLARE CurHistoryTable CURSOR FOR 

-- query system tables to get table schema
SELECT CONVERT(VARCHAR(100), SC.Name) AS FieldName, CONVERT(VARCHAR(50), ST.Name) AS DataType, 
    CONVERT(VARCHAR(10),SC.max_length) AS FieldLength, CONVERT(VARCHAR(10), SC.precision) AS FieldPrecision, 
    CONVERT(VARCHAR(10), SC.Scale) AS FieldScale, 
    CASE SC.Is_Nullable WHEN 1 THEN 'Y' ELSE 'N' END AS AllowNulls
FROM Sys.Objects SO
INNER JOIN Sys.Columns SC ON SO.object_ID = SC.object_ID
INNER JOIN Sys.Types ST ON SC.system_type_id = ST.system_type_id
WHERE SO.type = 'u' AND SO.Name = @TableName
ORDER BY SO.[name], SC.Column_Id ASC

OPEN CurHistoryTable

FETCH NEXT FROM CurHistoryTable INTO @FieldName, @DataType, 
    @FieldLength, @Precision, @Scale, @AllowNulls

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @HistoryTableName = @TableName + '_History'
    -- create list of table columns
    IF LEN(@FieldList) = 0
        BEGIN
            SET @FieldList = @FieldName
            SET @FirstField = @FieldName
        END
    ELSE
        BEGIN
            SET @FieldList = @FieldList + ', ' + @FieldName
        END

-- if we are at the start add the std audit columns in front
    IF LEN(@SQLTable) = 0
        BEGIN
            SET @SQLTable = 'CREATE TABLE [dbo].[' + @HistoryTableName + '] (' + @CRLF
            SET @SQLTable = @SQLTable + @TAB + '[Id] [INT] IDENTITY NOT NULL,' + @CRLF
            SET @SQLTable = @SQLTable + @TAB + '[Operation]' + @TAB + 'CHAR (1) NOT NULL,' + @CRLF
            SET @SQLTable = @SQLTable + @TAB + '[Date]' + @TAB + 'DATETIMEOFFSET NOT NULL,' + @CRLF
            SET @SQLTable = @SQLTable + @TAB + '['+ @TableName +'_Id]' + @TAB + '[INT] NOT NULL,' + @CRLF
        END

    -- SET @SQLTable = @TAB + @SQLTable + '/*@FieldName:'+ @FieldName +', @DataType: ' + @DataType +' @FieldLength: ' + @FieldLength +'*/' + @CRLF
    
    -- IGNORE 'sysname'
    IF UPPER(@DataType) IN ('SYSNAME')
        GOTO END_Gen
    
    -- IGNORE ID - we generate it ourselves.
    IF UPPER(@FieldName) IN ('Id')
        GOTO END_Gen
            
    SET @SQLTable = @SQLTable + @TAB + '[' + @FieldName + '] ' + '[' + @DataType + ']'
    
    IF UPPER(@DataType) IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR', 'BINARY')
        BEGIN
            -- //TODO: @FieldLength is double here - why?
            SET @SQLTable = @SQLTable + '(' + @FieldLength + ')'
        END
    ELSE IF UPPER(@DataType) IN ('DECIMAL', 'NUMERIC')
        BEGIN
            SET @SQLTable = @SQLTable + '(' + @Precision + ', ' + @Scale + ')'
        END
    
    SET @SQLTable = @SQLTable + ' NULL'

    SET @SQLTable = @SQLTable + ',' + @CRLF
    
    
    END_Gen:	
    FETCH NEXT FROM CurHistoryTable INTO @FieldName, @DataType, 
        @FieldLength, @Precision, @Scale, @AllowNulls
END

CLOSE CurHistoryTable
DEALLOCATE CurHistoryTable

-- finish history table script  and code for Primary key
SET @SQLTable = @SQLTable + ' )' + @CRLF + @CRLF
SET @SQLTable = @SQLTable + 'ALTER TABLE [dbo].[' + @HistoryTableName + ']' + @CRLF
SET @SQLTable = @SQLTable + @TAB + 'ADD CONSTRAINT [PK_' + @HistoryTableName + '_Id] PRIMARY KEY NONCLUSTERED ([Id] ASC)' + @CRLF
SET @SQLTable = @SQLTable + @TAB + 'WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY];' + @CRLF + @CRLF

-- add foreign key (removed to allow for deletion of entries in the "live" table)
--SET @SQLTable = @SQLTable + 'ALTER TABLE [dbo].[' + @HistoryTableName + '] WITH CHECK ADD' + @CRLF
--SET @SQLTable = @SQLTable + @TAB + 'CONSTRAINT [FK_' + @HistoryTableName + '_' + @TableName + ']' + @CRLF
--SET @SQLTable = @SQLTable + @TAB + 'FOREIGN KEY ([' + @TableName + '_Id])' + @CRLF
--SET @SQLTable = @SQLTable + @TAB + 'REFERENCES [dbo].[' + @TableName + '] ([Id])' + @CRLF + @CRLF

PRINT @SQLTable

-- execute sql script to create history table
IF @ExecuteProcedure = 'Y'
    EXEC(@SQLTable)

IF @@ERROR <> 0
    BEGIN
        PRINT '******************** ERROR CREATING HISTORY TABLE FOR TABLE: ' + @TableName + ' **************************************'
        RETURN -1
    END


SET @SQLTrigger = ''

IF @CreateTrigger = 'Y'
BEGIN
    -- create history trigger
    SET @SQLTrigger =               '/***********************************************************************************************' + @CRLF
    SET @SQLTrigger = @SQLTrigger + 'Created By: ' + SUSER_SNAME() + @CRLF
    SET @SQLTrigger = @SQLTrigger + 'Created On: ' + @Date + @CRLF
    SET @SQLTrigger = @SQLTrigger + 'Comments: Auto generated history trigger (GenerateHistoryTableAndTrigger)' + @CRLF
    SET @SQLTrigger = @SQLTrigger + '***********************************************************************************************/' + @CRLF
    SET @SQLTrigger = @SQLTrigger + 'CREATE TRIGGER [' + @TableName + '_HistoryTrigger] ON dbo.' + @TableName + @CRLF
    SET @SQLTrigger = @SQLTrigger + 'AFTER INSERT, UPDATE, DELETE ' + @CRLF
    SET @SQLTrigger = @SQLTrigger + 'AS' + @CRLF
    SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + 'DECLARE @DateNow datetimeoffset,' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + '        @DeletedCount int,' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + '        @InsertedCount int' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + '' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + '-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + 'SET NOCOUNT ON;' + @CRLF + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + 'SELECT @DateNow = SYSDATETIMEOFFSET()' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + 'SELECT @DeletedCount = Count(*) FROM deleted' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + 'SELECT @InsertedCount = Count(*) FROM inserted' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + '' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + 'INSERT [dbo].[' + @HistoryTableName + ']'+ @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + 'SELECT [Operation] = CASE' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + '            WHEN @DeletedCount > 0 and @InsertedCount > 0 THEN ''U''' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + '            WHEN @InsertedCount > 0                       THEN ''I''' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + '            ELSE                                               ''D'' END,' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + '       [Date] = @DateNow,' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + '       AuditedTable.*' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + 'FROM (' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + '    SELECT * FROM inserted' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + '    UNION' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + '    SELECT * FROM deleted' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + '        WHERE (Id NOT IN (SELECT Id from inserted))' + @CRLF
    SET @SQLTrigger = @SQLTrigger + @TAB + ' ) AS AuditedTable' + @CRLF
    SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF + @CRLF + @CRLF
    
    PRINT @SQLTrigger
    
    -- execute sql script to create update/delete trigger
    IF @ExecuteProcedure = 'Y'
        EXEC(@SQLTrigger)

    IF @@ERROR <> 0
        BEGIN
            PRINT '******************** ERROR CREATING HISTORY TRIGGER FOR TABLE: ' + @TableName + ' **************************************'
            RETURN -1
        END
END