I took this script and merged all the fixes in these comments and others found. Support audit in another database too if you want. Support updating the audit tables if new columns are added.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('Utility.GenerateAuditTrail') IS NULL
BEGIN
PRINT('Create procedure Utility.GenerateAuditTrail');
EXEC('CREATE PROCEDURE Utility.GenerateAuditTrail AS SET NOCOUNT ON;')
END
ELSE
PRINT('Alter procedure Utility.GenerateAuditTrail');
GO
ALTER PROCEDURE Utility.GenerateAuditTrail
@Owner VARCHAR(128)
,@TableName VARCHAR(128)
,@AuditNameExtension VARCHAR(128) = '_Audit'
,@DropAuditTable BIT = 0
,@AuditDatabaseName VARCHAR(128) = NULL
AS
BEGIN
IF @AuditDatabaseName IS NULL
SET @AuditDatabaseName = (SELECT DB_NAME())
ELSE
IF NOT EXISTS(SELECT name FROM sys.databases WHERE name = @AuditDatabaseName)
BEGIN
PRINT 'ERROR: Database''' + @AuditDatabaseName + ''' does not exist';
RETURN;
END
PRINT 'Current database : ' + @AuditDatabaseName;
IF NOT EXISTS(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'ERROR: Table does not exist';
RETURN;
END
IF @AuditNameExtension IS NULL
BEGIN
PRINT 'ERROR: @AuditNameExtension cannot be null';
RETURN;
END
DECLARE @ExecuteInAuditDatabase NVARCHAR(MAX);
SET @ExecuteInAuditDatabase = 'EXEC ' + @AuditDatabaseName + '..sp_executesql N''
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE [name] = ''''' + @Owner + ''''')
EXECUTE(N''''CREATE SCHEMA ' + @Owner + ';'''');''';
EXEC sp_executesql @ExecuteInAuditDatabase;
SET @ExecuteInAuditDatabase = '
IF (EXISTS(SELECT * FROM ' + @AuditDatabaseName + '.sys.sysobjects WHERE id = OBJECT_ID(N''[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'') AND OBJECTPROPERTY(id, N''IsUserTable'') = 1) AND ' + CAST(@DropAuditTable AS CHAR(1)) + ' = 1)
BEGIN
PRINT ''Dropping audit table [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '];
DROP TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'';
END';
EXEC sp_executesql @ExecuteInAuditDatabase;
DECLARE TableColumns CURSOR Read_Only
FOR SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
FROM
sys.sysobjects a
INNER JOIN sys.syscolumns b on a.id = b.id
INNER JOIN sys.systypes c on b.xusertype = c.xusertype and c.name <> 'sysname'
WHERE
a.id = OBJECT_ID(N'[' + @Owner + '].[' + @TableName + ']')
AND OBJECTPROPERTY(a.id, N'IsUserTable') = 1
ORDER BY
b.colId;
OPEN TableColumns;
DECLARE @ColumnName VARCHAR(128);
DECLARE @ColumnType VARCHAR(128);
DECLARE @ColumnLength SMALLINT;
DECLARE @ColumnNullable INT;
DECLARE @ColumnCollation SYSNAME;
DECLARE @ColumnPrecision TINYINT;
DECLARE @ColumnScale TINYINT;
DECLARE @CreateStatement VARCHAR(MAX);
DECLARE @ListOfFields VARCHAR(MAX);
SET @ListOfFields = '';
DECLARE @IsAuditTableExistsInAuditDatabase BIT;
SET @ExecuteInAuditDatabase = 'EXEC ' + @AuditDatabaseName + '..sp_executesql N''' + '
IF EXISTS(SELECT 1 FROM sys.sysobjects WHERE id = OBJECT_ID(N''''[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'''') AND OBJECTPROPERTY(id, N''''IsUserTable'''') = 1) SET @IsAuditTableExistsInAuditDatabase = 1; ELSE SET @IsAuditTableExistsInAuditDatabase = 0;'', N''@IsAuditTableExistsInAuditDatabase BIT OUTPUT'', @IsAuditTableExistsInAuditDatabase OUTPUT'
EXEC sp_executesql @ExecuteInAuditDatabase, N'@IsAuditTableExistsInAuditDatabase BIT OUTPUT', @IsAuditTableExistsInAuditDatabase OUTPUT;
IF @IsAuditTableExistsInAuditDatabase = 1
BEGIN
PRINT 'Table already exists. Will update table schema with new fields.';
DECLARE @NewFields VARCHAR(MAX) = '',
@ExistingFields VARCHAR(MAX)
SET @ExecuteInAuditDatabase = 'EXEC ' + @AuditDatabaseName + '..sp_executesql N''' + '
DECLARE @ExcludeColumnNames VARCHAR(MAX) = '''',AuditId,AuditAction,AuditDate,AuditUtcDate,AuditUser,AuditApp,AuditTransactionId,''''
SELECT
@ExistingFields = COALESCE(@ExistingFields, '''''''') + '''','''' + sc.name + '''',''''
FROM
sysobjects so
INNER JOIN syscolumns sc on so.id = sc.id
WHERE
so.id = OBJECT_ID(N''''[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'''')
AND OBJECTPROPERTY(so.id, N''''IsUserTable'''') = 1
AND CHARINDEX('''','''' + sc.name + '''','''', @ExcludeColumnNames) = 0
ORDER BY
sc.colId'', N''@ExistingFields VARCHAR(MAX) OUTPUT'', @ExistingFields OUTPUT;';
EXEC sp_executesql @ExecuteInAuditDatabase, N'@ExistingFields VARCHAR(MAX) OUTPUT', @ExistingFields OUTPUT;
PRINT 'Existing fields : ' + ISNULL(@ExistingFields, '');
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + ',[' + @ColumnName + ']';
END
IF (CHARINDEX(',' + @ColumnName + ',', @ExistingFields) = 0)
BEGIN
SET @NewFields = @NewFields + ',[' + @ColumnName + '] [' + @ColumnType + '] ';
IF @ColumnType in ('binary', 'char', 'varbinary', 'varchar')
BEGIN
IF (@ColumnLength = -1)
Set @NewFields = @NewFields + '(MAX) ';
ELSE
SET @NewFields = @NewFields + '(' + cast(@ColumnLength as varchar(10)) + ') ';
END
IF @ColumnType in ('nchar', 'nvarchar')
BEGIN
IF (@ColumnLength = -1)
Set @NewFields = @NewFields + '(MAX) ';
ELSE
SET @NewFields = @NewFields + '(' + cast((@ColumnLength / 2 ) as varchar(10)) + ') ';
END
IF @ColumnType in ('decimal', 'numeric')
SET @NewFields = @NewFields + '(' + cast(@ColumnPrecision as varchar(10)) + ',' + cast(@ColumnScale as varchar(10)) + ') ';
IF @ColumnType in ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
SET @NewFields = @NewFields + 'COLLATE ' + @ColumnCollation + ' ';
SET @NewFields = @NewFields + 'NULL';
END
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale;
END
IF LEN(@NewFields) > 0
BEGIN
SET @CreateStatement = 'ALTER TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '] ADD ' + SUBSTRING(@NewFields, 2, LEN(@NewFields));
PRINT 'Adding new Fields to audit table [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'
PRINT @CreateStatement
EXEC (@CreateStatement)
END
ELSE
PRINT 'No new fields to add to the audit table'
END
ELSE
BEGIN
SET @CreateStatement = 'CREATE TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '] (';
SET @CreateStatement = @CreateStatement + '[AuditId] [BIGINT] IDENTITY (1, 1) NOT NULL';
SET @CreateStatement = @CreateStatement + ',[AuditAction] [CHAR] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL';
SET @CreateStatement = @CreateStatement + ',[AuditDate] [DATETIME] NOT NULL';
SET @CreateStatement = @CreateStatement + ',[AuditUser] [VARCHAR] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL';
SET @CreateStatement = @CreateStatement + ',[AuditApp] [VARCHAR](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL' ;
SET @CreateStatement = @CreateStatement + ',[AuditTransactionId] [BIGINT] NOT NULL';
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + ',[' + @ColumnName + ']';
SET @CreateStatement = @CreateStatement + ',[' + @ColumnName + '] [' + @ColumnType + '] ';
IF @ColumnType in ('binary', 'char', 'varbinary', 'varchar')
BEGIN
IF (@ColumnLength = -1)
Set @CreateStatement = @CreateStatement + '(MAX) ';
ELSE
SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnLength as varchar(10)) + ') ';
END
IF @ColumnType in ('nchar', 'nvarchar')
BEGIN
IF (@ColumnLength = -1)
Set @CreateStatement = @CreateStatement + '(MAX) ';
ELSE
SET @CreateStatement = @CreateStatement + '(' + cast((@ColumnLength / 2 ) as varchar(10)) + ') ';
END
IF @ColumnType in ('decimal', 'numeric')
SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnPrecision as varchar(10)) + ',' + cast(@ColumnScale as varchar(10)) + ') ';
IF @ColumnType in ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' ';
SET @CreateStatement = @CreateStatement + 'NULL';
END
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale;
END
SET @CreateStatement = @CreateStatement + ')';
PRINT 'Creating audit table [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']';
PRINT @CreateStatement;
EXEC (@CreateStatement);
SET @CreateStatement = 'ALTER TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '] ADD ';
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditDate] DEFAULT (GETDATE()) FOR [AuditDate]';
SET @CreateStatement = @CreateStatement + ',CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditUser] DEFAULT (SUSER_SNAME()) FOR [AuditUser]';
SET @CreateStatement = @CreateStatement + ',CONSTRAINT [PK_' + @TableName + @AuditNameExtension + '] PRIMARY KEY CLUSTERED ([AuditId]) ON [PRIMARY]';
SET @CreateStatement = @CreateStatement + ',CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditApp] DEFAULT (''App=('' + RTRIM(ISNULL(APP_NAME(),'''')) + '') '') for [AuditApp]';
SET @CreateStatement = @CreateStatement + ',CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditTransactionId] DEFAULT (0) FOR [AuditTransactionId]';
PRINT 'Setting primary key and default values'
PRINT @CreateStatement;
EXEC (@CreateStatement);
END
CLOSE TableColumns;
DEALLOCATE TableColumns;
PRINT 'Dropping triggers'
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[TR_Audit_' + @TableName + '_Insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('DROP TRIGGER [' + @Owner + '].[TR_Audit_' + @TableName + '_Insert]');
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[TR_Audit_' + @TableName + '_Update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('DROP TRIGGER [' + @Owner + '].[TR_Audit_' + @TableName + '_Update]');
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[TR_Audit_' + @TableName + '_Delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('DROP TRIGGER [' + @Owner + '].[TR_Audit_' + @TableName + '_Delete]');
PRINT 'Creating triggers';
EXEC ('CREATE TRIGGER TR_Audit_' + @TableName + '_Insert ON ' + @Owner + '.' + @TableName + ' FOR INSERT AS BEGIN /*DECLARE @TransactionId BIGINT; SELECT @TransactionId = transaction_id FROM sys.dm_tran_current_transaction;*/ INSERT INTO [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](AuditAction/*, AuditTransactionId*/' + @ListOfFields + ') SELECT ''I''/*, @TransactionId*/' + @ListOfFields + ' FROM Inserted; END');
EXEC ('CREATE TRIGGER TR_Audit_' + @TableName + '_Update ON ' + @Owner + '.' + @TableName + ' FOR UPDATE AS BEGIN /*DECLARE @TransactionId BIGINT; SELECT @TransactionId = transaction_id FROM sys.dm_tran_current_transaction;*/ INSERT INTO [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](AuditAction/*, AuditTransactionId*/' + @ListOfFields + ') SELECT ''U''/*, @TransactionId*/' + @ListOfFields + ' FROM Inserted; END');
EXEC ('CREATE TRIGGER TR_Audit_' + @TableName + '_Delete ON ' + @Owner + '.' + @TableName + ' FOR DELETE AS BEGIN /*DECLARE @TransactionId BIGINT; SELECT @TransactionId = transaction_id FROM sys.dm_tran_current_transaction;*/ INSERT INTO [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](AuditAction/*, AuditTransactionId*/' + @ListOfFields + ') SELECT ''D''/*, @TransactionId*/' + @ListOfFields + ' FROM Deleted; END');
END
GO
Here a script to create/update all audit tables :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('Utility.UpdateAllAuditTrails') IS NULL
BEGIN
PRINT('Create procedure Utility.UpdateAllAuditTrails');
EXEC('CREATE PROCEDURE Utility.UpdateAllAuditTrails AS SET NOCOUNT ON;')
END
ELSE
PRINT('Alter procedure Utility.UpdateAllAuditTrails');
GO
ALTER PROCEDURE Utility.UpdateAllAuditTrails
@RecreateAuditTables BIT = 0
AS
BEGIN
DECLARE @output NVARCHAR(MAX) = N'';
;WITH
Tables AS
(
SELECT
s.name AS [Schema]
,t.name AS [Table]
FROM
sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
CHARINDEX(',' + s.name + ',', ',IgnoreSchema1,
,IgnoreSchema2,
,IgnoreSchema3,
') = 0
AND CHARINDEX(',' + t.name + ',', ',IgnoreTable1,
,IgnoreTable2,
,IgnoreTable3,
') = 0
AND t.name NOT LIKE '%Logs'
AND t.name NOT LIKE '%_Audit'
)
SELECT
@output += CHAR(13) + CHAR(10) + 'EXEC Utility.GenerateAuditTrail ''' + t.[Schema] + ''', ''' + t.[Table] + ''', @DropAuditTable=' + CAST(@RecreateAuditTables AS CHAR(1)) + ', @AuditDatabaseName=''' + (SELECT DB_NAME() + '_Audit') + ''';'
FROM
Tables t
ORDER BY t.[Schema]
,t.[Table]
PRINT 'Output : ' + @output;
EXEC sp_executesql @output;
END
GO
|