declare @SPPrefix varchar(5) declare @Author nvarchar (250) declare @Schema nvarchar(100) declare @DisplaySQL bit declare @ExecuteSQL bit declare @CreationStyle int declare @DateStyle nvarchar(max) ------------------------------------------------------------------------- -- -- Script settings begin -- -- Original location : http://www.glossover.co.uk/archives/357 -- -- Please update the script settings as needed. ------------------------------------------------------------------------- set @Author = SUSER_NAME() set @SPPrefix = 'usp_' set @Schema = 'dbo' set @DisplaySQL = 1 set @ExecuteSQL = 0 --@CreationStyle --1 - Create only audit tables where no audit tables are in existence. --2 - Create all audit tables by dropping existing audit tables. --3 - Create all audit tables and place current audit tables into archive e.g. _Archive to _Archive_+@DateStyle set @CreationStyle = 1 --The date style for the _Archive rename as follows. set @DateStyle = convert(varchar, getdate(), 9) ------------------------------------------------------------------------- -- -- Script settings end -- ------------------------------------------------------------------------- ------------------------------------------------------------------------- -- -- Do not edit below this point. -- ------------------------------------------------------------------------- set NOCOUNT on declare @tableName nvarchar(max) = '' declare cursorTables CURSOR FOR select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME != 'sysdiagrams' and TABLE_NAME not like '%_Audit%' and TABLE_SCHEMA = @Schema order by TABLE_NAME open cursorTables fetch next from cursorTables into @tableName while @@fetch_status = 0 begin create table #TableInformation ( myRowCount int, tableName nvarchar(max), schemaName nvarchar(max), columnName nvarchar(max), isPrimaryKey bit, dataType nvarchar(max), defaultValue nvarchar(max) ) insert into #TableInformation ( myRowCount, SchemaName, tableName, columnName, DataType, isPrimaryKey, defaultValue ) SELECT INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION, INFORMATION_SCHEMA.COLUMNS.table_schema, INFORMATION_SCHEMA.COLUMNS.TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, information_schema.COLUMNS.DATA_TYPE + ' ' + case when information_schema.COLUMNS.DATA_TYPE IN (N'numeric', N'decimal') then '(' + cast( information_schema.COLUMNS.NUMERIC_PRECISION as varchar ) + ', ' + cast( information_schema.COLUMNS.NUMERIC_SCALE as varchar ) + ') ' when information_schema.COLUMNS.DATA_TYPE IN (N'datetime2', N'datetimeoffset', N'time') then '(' + cast( information_schema.COLUMNS.DATETIME_PRECISION as varchar ) + ') ' when information_schema.COLUMNS.DATA_TYPE IN (N'geography', N'geometry', N'hierarchyid', N'image', N'ntext', N'sql_variant', N'text', N'xml') then '' else case when information_schema.COLUMNS.CHARACTER_MAXIMUM_LENGTH is not null then REPLACE('('+ CAST( information_schema.COLUMNS.CHARACTER_MAXIMUM_LENGTH as varchar) +')', '(-1)', '(max)') else '' end end , case when INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE is not null then 1 else 0 end as IsPrimaryKey, information_schema.COLUMNS.COLUMN_DEFAULT as defaultValue FROM INFORMATION_SCHEMA.COLUMNS LEFT OUTER JOIN information_schema.KEY_COLUMN_USAGE ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME=information_schema.KEY_COLUMN_USAGE.TABLE_NAME AND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME=information_schema.KEY_COLUMN_USAGE.COLUMN_NAME AND INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION=information_schema.KEY_COLUMN_USAGE.ORDINAL_POSITION LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON information_schema.KEY_COLUMN_USAGE.TABLE_NAME=information_schema.TABLE_CONSTRAINTS.TABLE_NAME AND information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME=information_schema.TABLE_CONSTRAINTS.CONSTRAINT_NAME WHERE information_schema.COLUMNS.TABLE_NAME= @tableName and INFORMATION_SCHEMA.COLUMNS.table_schema = @Schema ORDER BY information_schema.COLUMNS.ORDINAL_POSITION declare @PKColumn as nvarchar(max) declare @PKColumnType as nvarchar(max) declare @SPText as nvarchar(max) declare @NonAutiteColumnTypes as int set @SPText = '' -- --Set Schema & primary key values -- set @PKColumn = null set @PKColumnType = null set @NonAutiteColumnTypes = 0 select @PKColumn = columnName, @PKColumnType = rtrim(ltrim(dataType)) from #TableInformation where isPrimaryKey = 1 select @NonAutiteColumnTypes = COUNT(*) from #TableInformation where dataType in (N'text', N'ntext', N'image', N'timestamp') if @PKColumn is null begin print '' print '---------------------------------------------------------------------------------------------------------' print '--There is no primary key on the "' + @tableName + '" the audit process will not be run for this table.' print '---------------------------------------------------------------------------------------------------------' print '' end else if @NonAutiteColumnTypes > 0 begin print '' print '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------' print '--The table"' + @tableName + '" contains one of the following types text, ntext, image, timestamp types that can not be used in the inserted or deleted tables needed for the audit trigger to function.' print '--The trigger can be hand crafted so it will not contain the restricted data types but this will need to be hand crafted and is not suited for an automated script.' print '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------' print '' end else begin if @CreationStyle = 3 begin --If there is an audit table rename it. set @SPText = 'if exists (select * from sys.objects where object_id = OBJECT_ID(N''['+ @Schema +'].['+ @tableName +'_Audit]'') AND type in (N''U'')) exec SP_RENAME ['+ @tableName +'_Audit], ['+ @tableName +'_Audit_'+ @DateStyle +']' if @DisplaySQL = 1 print @SPText + ' go ' if @ExecuteSQL = 1 EXEC sp_executesql @SPText end if @CreationStyle = 2 begin set @SPText = 'if exists (select * from sys.objects where object_id = OBJECT_ID(N''['+ @Schema +'].['+ @tableName +'_Audit]'') AND type in (N''U'')) drop TABLE ['+ @Schema +'].['+ @tableName +'_Audit]' if @DisplaySQL = 1 print @SPText + ' go ' if @ExecuteSQL = 1 EXEC sp_executesql @SPText end declare @standardColumns as nvarchar(max) declare @insertedColumns as nvarchar(max) declare @deletedColumns as nvarchar(max) declare @Line as nvarchar(max) declare @i as int declare @max int set @insertedColumns = '' set @deletedColumns = '' set @standardColumns = ' [timestamp] datetime2, [UpdatedBy] nvarchar(250), [ComputerName] nvarchar(250), [ApplicationName] nvarchar(250), [Action] char (1),' --Get all of the column names & data types. select @i = min(myRowCount), @max = max(myRowCount) from #TableInformation while @i <= @max begin set @Line = '' if @i = @max begin select @insertedColumns = @insertedColumns + ' [Inserted_' + columnName + '] ' + dataType , @deletedColumns = @deletedColumns + ' [Deleted_' + columnName + '] ' + dataType from #TableInformation where myRowCount = @i end else begin select @insertedColumns = @insertedColumns + ' [Inserted_' + columnName + '] ' + dataType + ' , ', @deletedColumns = @deletedColumns + ' [Deleted_' + columnName + '] ' + dataType + ' , ' from #TableInformation where myRowCount = @i end set @i = @i + 1 end set @SPText = 'create table ['+@Schema+'].['+@TABLENAME+'_Audit] (' + @standardColumns + @insertedColumns + ', ' + @deletedColumns +')' if @DisplaySQL = 1 print @SPText + ' go ' if @ExecuteSQL = 1 EXEC sp_executesql @SPText set @SPText = 'if exists (select * from sys.triggers where object_id = OBJECT_ID(N''['+ @Schema + '].[trg_'+ @tableName +'_Audit]'')) drop trigger ['+ @Schema +'].[trg_'+ @tableName +'_Audit]' if @DisplaySQL = 1 print @SPText + ' go ' if @ExecuteSQL = 1 EXEC sp_executesql @SPText set @SPText = 'CREATE trigger [trg_'+ @tableName +'_Audit] ON ['+ @Schema +'].['+ @tableName +'] AFTER update, insert, delete AS begin set NOCOUNT ON; INSERT INTO ' + @tableName + '_Audit select getdate(), (suser_sname()), HOST_NAME(), APP_NAME(), action = case when inserted.'+ @PKColumn +' is NULL then ''D'' when deleted.'+ @PKColumn +' is NULL then ''I'' else ''U'' end, inserted.*, deleted.* from inserted full outer join deleted on inserted.'+ @PKColumn +' = deleted.'+ @PKColumn +' END' if @DisplaySQL = 1 print @SPText + ' go ' if @ExecuteSQL = 1 EXEC sp_executesql @SPText end drop table #TableInformation fetch next from cursorTables into @tableName end CLOSE cursorTables DEALLOCATE cursorTables