|
many thanks with appreciations
|
|
|
|
|
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
|
|
|
|
|
Using GetDate() to determine the order of events is dangerous. All datetimes stored in a database should be stored in UTC and converted to local time at the client - this is databases 101.
Consider what happens at a daylight-saving switch and the clocks go back...
Event 1: 02:59:50
Event 2: 02:59:59
Event 3: 02:00:01
You've just screwed your audit records.
Always store time in UTC - GetUtcDate() instead on GetDate()
Convert to local time on display (local being defined by the user looking at the data)
Also consider using DATETIME2 instead of DATETIME.
modified 15-Sep-16 17:50pm.
|
|
|
|
|
I received an error on executing this Stored Procedure
"Invalid Column name....."
After some research i found that variable "@ListOfFields" was causing the issue
Since i had more than 100 columns in my source table what caused the @ListOfFields varchar(2000) to exceed more than 2000 character so list of column names got truncated.
So Update the Variable @ListOfFields to varchar(max).
And Great Job. This made my life easy to maintain the log....
|
|
|
|
|
|
Great Job! I added one modification to fit into my project, this change is handling the case the source table change (like when upgrading software) it is updating the schema instead of deleting the table. This way we keep the records, if any field no longer exists it remains in the audit table but is excluded from the listOfFields so triggers will ignore it.
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'Table already exists. Will update table schema with new fields.'
DECLARE @ExcludeColumnNames VARCHAR(2000),
@NewFields VARCHAR(2000) = '',
@ExistingFields VARCHAR(2000)
SET @ExcludeColumnNames = 'AuditId,AuditAction,AuditDate,AuditUtcDate,AuditUser,AuditApp'
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 + @AuditNameExtention + ']')
AND OBJECTPROPERTY(so.id, N'IsUserTable') = 1
AND CHARINDEX(sc.name, @ExcludeColumnNames) = 0
ORDER BY sc.colId
SET @CreateStatement = 'ALTER TABLE [' + @Owner + '].[' + @TableName + @AuditNameExtention + '] ADD '
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 + @SourceTableAlias + '.' + @ColumnName + ','
... More statements here
END
END
SET @CreateStatement = LEFT(@CreateStatement, DATALENGTH(@CreateStatement) -1)
IF(LEN(@NewFields) > 0)
BEGIN
PRINT 'Adding new Fields to audit table [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']'
PRINT @CreateStatement
EXEC (@CreateStatement)
END
END
|
|
|
|
|
Do you have the full script? Because the snippet is incomplete, missing variables, missing parts. I think you pasted a wrong/old copy of the script you had...
|
|
|
|
|
Instead of suser_sname() I'd like to capture the hostname of the client. However, Host_Name() only seems to capture the hostname of the server itself.
|
|
|
|
|
I receive the follow error when attempting to create a _shadow table on certain tables. From trying to troubleshoot I think this is because of the size of the generated SP? Any help/tip appreciated. If there's specific info I can post to help please let me know.
Msg 2717, Level 16, State 2, Line 1
The size (8000) given to the parameter 'Summary' exceeds the maximum allowed (4000).
Best Regards,
Chris
Update: I changed the EXEC statements to PRINT, manually changed the fields where the auto generated fields were to long and created _shadow table manually.
modified 1-Aug-15 17:36pm.
|
|
|
|
|
---------------------------
Microsoft SQL Server Management Studio
---------------------------
No rows were deleted.
A problem occurred attempting to delete row 1.
Error Source: Microsoft.SqlServer.Management.DataTools.
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows).
Correct the errors and attempt to delete the row again or press ESC to cancel the change(s).
---------------------------
OK Help
---------------------------
Fix For the above Error:
=======================
addition of SET NOCOUNT ON; for each triggers fixes.
|
|
|
|
|
can you tell me where to insert the set nocount on; in the code? thanks
|
|
|
|
|
Were you able to fix this? I am also facing this issue and I don't get why this is happening...
|
|
|
|
|
Thank you very much for taking the time to code and post this example. I am using it now to track down a problem of table rows inexplicable disappearing.
|
|
|
|
|
Hi Cedric
Fantastic job, well illustrated and very well explained.
Keep up the good work !
Marc-Olivier
Web and application developer
marckwest001 @yahoo.com
|
|
|
|
|
Very good article Code worked like advertised on SQL 2012 also.
|
|
|
|
|
Effective and simple implementation of an audit trail!
|
|
|
|
|
Thank you for a great solution!
I encountered one issue regarding the length of varchar and nvarchar columns. GenerateAuditTrail generates columns with wrong length for nchar and nvarchar columns.
SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
FROM sysobjects a
inner join syscolumns b on a.id = b.id
inner join systypes c on b.xtype = c.xtype and c.name <> 'sysname'
WHERE a.id = object_id(N'[' + @Owner + '].[' + @TableName + ']')
and OBJECTPROPERTY(a.id, N'IsUserTable') = 1
ORDER BY b.colId
b.Length in this case returns bytes needed in order to contain the value and not the character length. An varchar(10) has a length of 10 bytes. nvarchar(10), however, has a byte length of 20 bytes. In order to generate shadow tables with the right column length, you have to treat nachr and nvarchar columns differently.
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
For ncahr and nvarchar fields, you need to divide the length by two.
Best
Nasser
|
|
|
|
|
|
I just realized after I posted it that the code may not work for fields with special characters. You can change the CHARINDEX() code to
PATINDEX('%[^a-z]%', @ColumnName) > 0
SET @ListOfFields = @ListOfFields + CASE WHEN PATINDEX('%[^a-z]%', @ColumnName) > 0 THEN QUOTENAME(@ColumnName) ELSE @ColumnName END + ','
SET @CreateStatement = @CreateStatement + CASE WHEN PATINDEX('%[^a-z]%', @ColumnName) > 0 THEN QUOTENAME(@ColumnName) ELSE @ColumnName END + ' ' + @ColumnType
|
|
|
|
|
Thanks, I had this same problem.
Do you know if the original creator of the procedure still is around to read/update the solution?
|
|
|
|
|
I ran into an issue with tables with a LOT of columns, so I modified the code to remove '[ ]' where not needed as well as the section where it adds NULL/NOT NULL. Since NULL is the default, I removed it from the generated code.
CASE WHEN CHARINDEX(' ', @ColumnName) = 0 THEN @ColumnName ELSE QUOTENAME(@ColumnName) END
IF @ColumnNullable = 0
SET @CreateStatement = @CreateStatement + ' NOT NULL'
SET @CreateStatement = @CreateStatement + ', '
Here is the whole code:
CREATE PROCEDURE [dbo].[sp_GenerateShadowTrail]
@TableName varchar(128),
@Owner varchar(128) = 'dbo',
@AuditNameExtention varchar(128) = '_Shadow',
@DropAuditTable bit = 0
AS
BEGIN
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 @AuditNameExtention is null
BEGIN
PRINT 'ERROR: @AuditNameExtention cannot be null'
RETURN
END
IF (exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) and @DropAuditTable = 1)
BEGIN
PRINT 'Dropping audit table [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']'
EXEC ('drop table ' + @TableName + @AuditNameExtention)
END
DECLARE TableColumns CURSOR Read_Only
FOR SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
FROM sysobjects a
inner join syscolumns b on a.id = b.id
inner join systypes c on b.xtype = c.xtype 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 nvarchar(250)
DECLARE @ColumnType nvarchar(100)
DECLARE @ColumnLength smallint
DECLARE @ColumnNullable int
DECLARE @ColumnCollation sysname
DECLARE @ColumnPrecision tinyint
DECLARE @ColumnScale tinyint
DECLARE @CreateStatement nvarchar(MAX)
DECLARE @ListOfFields nvarchar(4000)
SET @ListOfFields = ''
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'Table already exists. Only triggers will be updated.'
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 + CASE WHEN CHARINDEX(' ', @ColumnName) = 0 THEN @ColumnName ELSE QUOTENAME(@ColumnName) END + ','
END
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
END
END
ELSE
BEGIN
SET @CreateStatement = 'CREATE TABLE [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']('
SET @CreateStatement = @CreateStatement + 'AuditId bigint IDENTITY (1,1) 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 + CASE WHEN CHARINDEX(' ', @ColumnName) = 0 THEN @ColumnName ELSE QUOTENAME(@ColumnName) END + ','
SET @CreateStatement = @CreateStatement + CASE WHEN CHARINDEX(' ', @ColumnName) = 0 THEN @ColumnName ELSE QUOTENAME(@ColumnName) END + ' ' + @ColumnType
IF @ColumnType in ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar')
BEGIN
IF (@ColumnLength = -1)
Set @CreateStatement = @CreateStatement + '(max) '
ELSE
SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnLength as varchar(10)) + ')'
END
IF @ColumnType in ('decimal', 'numeric')
SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnPrecision as varchar(10)) + ',' + cast(@ColumnScale as varchar(10)) + ')'
IF @ColumnNullable = 0
SET @CreateStatement = @CreateStatement + ' NOT NULL'
SET @CreateStatement = @CreateStatement + ', '
END
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
END
SET @CreateStatement = @CreateStatement + 'AuditAction char(1),'
SET @CreateStatement = @CreateStatement + 'AuditDate datetime NOT NULL ,'
SET @CreateStatement = @CreateStatement + 'AuditUser varchar(64),'
SET @CreateStatement = @CreateStatement + 'AuditApp varchar(128))'
PRINT 'Creating audit table [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']'
EXEC (@CreateStatement)
SET @CreateStatement = 'ALTER TABLE ' + @Owner + '.[' + @TableName + @AuditNameExtention + '] ADD '
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditDate] DEFAULT (getdate()) FOR AuditDate,'
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditUser] DEFAULT (suser_sname()) FOR AuditUser,CONSTRAINT [PK_' + @TableName + @AuditNameExtention + '] PRIMARY KEY CLUSTERED '
SET @CreateStatement = @CreateStatement + '([AuditId]) ON [PRIMARY], '
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditApp] DEFAULT (''App=('' + rtrim(isnull(app_name(),'''')) + '') '') for [AuditApp]'
EXEC (@CreateStatement)
END
CLOSE TableColumns
DEALLOCATE TableColumns
PRINT 'Dropping triggers'
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Insert]')
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Update]')
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Delete]')
PRINT 'Creating triggers'
EXEC ('CREATE TRIGGER tr_' + @TableName + '_Insert ON ' + @Owner + '.' + @TableName + ' FOR INSERT AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''I'' FROM Inserted')
EXEC ('CREATE TRIGGER tr_' + @TableName + '_Update ON ' + @Owner + '.' + @TableName + ' FOR UPDATE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''U'' FROM Inserted')
EXEC ('CREATE TRIGGER tr_' + @TableName + '_Delete ON ' + @Owner + '.' + @TableName + ' FOR DELETE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''D'' FROM Deleted')
END
|
|
|
|
|
yes! this worked. thanks.
|
|
|
|
|
I have a column name with a space in it [Name Here] and when I execute the SP it chokes on creating the triggers. I went to the trigger code and added +'['+@tablename+']'+ but that doesn't seem to do the trick. It also chokes on the audit name exension. Any advice?
SQL 2008
Table already exists. Only triggers will be updated.
Dropping triggers
Creating triggers
Msg 102, Level 15, State 1, Procedure tr_, Line 1
Incorrect syntax near 'CaseDetails'.
Msg 102, Level 15, State 1, Procedure tr_, Line 1
Incorrect syntax near '_shadow'.
|
|
|
|
|
Nice implementation, Very well done!
|
|
|
|
|
To reflect the correct user that triggered a delete a common solution is to update the ChangedBy column in the original record via the stored procedure before the delete is executed. A drawback is that the update of the ChangedBy field before the actual delete is also audited as a separate entry in the audit table. I therefore changed the Update trigger code. Since the update is reflected in the deleted and inserted tables I compare both and prevent to write in the audit table when the ChangedBy field is updated only. My first solution was flawed (don't program when you are too tired ). For my second approach I needed a user function to find the Primary Key fields. I joined the inserted and deleted tables on the primary keys and only imported those row that differed in one filed except the ChangedBy field. There might be abetter solution, but it worked for me.
Of course, the solution doesn't record changes when the ChangedBy column would be altered manually, for example in the SQL Management Console, but for my application this is not an issue. Optional the select could be extended to write a change of the ChangedBy column if the ChangedBy column is identical to the suser_name.
Change of field lists generation:
IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + '[' + @ColumnName + '],';
if (@ListOfFieldsi <> '')
Set @ListOfFieldsi = @ListOfFieldsi + ', '
SET @ListOfFieldsi = @ListOfFieldsi + 'i.[' + @ColumnName + ']';
if (@ColumnName <> @ChangedByColumnName)
begin
if (dbo.IsPrimaryColumn(@Tablename, @ColumnName) =1)
begin
if (@ListOfJoinPKs <> '')
Set @ListOfJoinPKs = @ListOfJoinPKs + ' AND '
SET @ListOfJoinPKs = @ListOfJoinPKs + 'i.' + '[' + @ColumnName + ']=' + 'd.[' + @ColumnName + ']'
end
else
begin
if (@ListOfWhereColumns <> '')
Set @ListOfWhereColumns = @ListOfWhereColumns + ' OR '
SET @ListOfWhereColumns = @ListOfWhereColumns + 'i.' + '[' + @ColumnName + ']<>' + 'd.[' + @ColumnName + ']'
end
end
END
Creation of Update Trigger:
Set @UpdateTrigger = 'CREATE TRIGGER tr_' + @TableName + '_Update ON ' + @Owner + '.' + @TableName + ' FOR UPDATE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFieldsi + ',''U'' FROM Inserted i inner join Deleted d '
if (@ListOfJoinPKs <>'')
Set @UpdateTrigger = @UpdateTrigger + 'ON ' + @ListOfJoinPKs
if (@ListOfWhereColumns<>'')
Set @UpdateTrigger = @UpdateTrigger + ' WHERE ' + @ListOfWhereColumns
EXEC (@UpdateTrigger)
EXEC ('CREATE TRIGGER tr_' + @TableName + '_Delete ON ' + @Owner + '.' + @TableName + ' FOR DELETE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''D'' FROM Deleted')
function IsPrimaryKey
CRETAE FUNCTION [dbo].[IsPrimaryColumn]
(
-- Add the parameters for the function here
@TableName varchar(128),
@ColumnName varchar(128)
)
RETURNS int
AS
BEGIN
Declare @out as bit
--Declare helper to identify the position of the delim
if (exists (SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K
ON C.TABLE_NAME = K.TABLE_NAME
AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND K.COLUMN_NAME = @ColumnName
and K.TABLE_NAME=@TableName))
Set @out=1
else
Set @out=0;
Return @out
END
|
|
|
|
|