I want to get all the transactions applied on a specific table in SQL Server 2008.
I found the last time a table was updated using this script:
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'DBName')
AND OBJECT_ID=OBJECT_ID('tableName')
I want to know all the transactions (Inserts, Updates, Deletes) for that table, and their datetime, and the query applied.
What is the best way to do this?
The only way to do this in a reasonable amount of time is to use a third party tool(as Martin said in first comment) such as ApexSQL Log that can read transaction log and get the information you need.
Note that in order for this to work your database has to be in a full recovery mode because that’s when SQL Server logs full transaction details that can be reconstructed later.
Another option is to investigate how to use undocumented fn_dblog function but this will take you a lot more time and you won’t be able to read detached logs or transaction log backups.
creating a trigger which will create a new table Emp_audit and add new tuples to it whenever any change is made to table employee
create trigger my_trigger on Employees
AFTER INSERT, UPDATE, DELETE
AS
DECLARE @What varchar(30);
DECLARE @Who varchar(30);
DECLARE @for int;
DECLARE @At time;
DECLARE @COUNTI int;
DECLARE @COUNTD int;
select @COUNTI = COUNT(*) from inserted;
select @COUNTD = COUNT(*) from deleted;
set @Who = SYSTEM_USER;
set @At = CURRENT_TIMESTAMP;
if( @COUNTD = 0 and @COUNTI = 1)
begin
set @What = 'insert';
select @for = EmployeeID from inserted i;
end
else
begin
if( @COUNTD = 1 and @COUNTI = 0)
begin
set @What = 'delete';
select @for = EmployeeID from deleted i;
end
else
begin
set @What = 'update';
select @for = EmployeeID from inserted i;
end
end
INSERT INTO EMP_Audit Values (@What, @Who, @for, @At);
You could create your own transaction logs
Step 1: Create your own table for transaction logs
CREATE TABLE [dbo].[TransactionLogs](
[TransactionLogID] [bigint] IDENTITY(1,1) NOT NULL,
[Query] [nvarchar](max) NOT NULL,
[DateCreated] [datetime] NOT NULL,
CONSTRAINT [PK_TransactionLogs] PRIMARY KEY CLUSTERED
(
[TransactionLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Step 2: Create stored procedure that create logs. (Note: Replace YourTablePKColumn with your table primary key column.)
create procedure [dbo].[sp_CreateQueryLogs]
(
@Query nvarchar(max) = null output,
@TableName nvarchar(100),
@YourTablePKColumn nvarchar(30),
@QueryTypeID tinyint --0 insert, 1 update, 2 delete
)
as
begin
declare @object_id bigint, @column_name nvarchar(100), @collation_name nvarchar(50), @column_name_id nvarchar(100) = null, @column_names nvarchar(max) = '', @column_values nvarchar(max) = '', @column_names_create nvarchar(max) = '', @values nvarchar(max) = '', @user_type_id int, @max_length nvarchar(10), @type_name nvarchar(50), @CreateTempTable nvarchar(max) = '', @is_nullable bit, @value nvarchar(max) = ''
create table #tmpValues(ColumnValues nvarchar(max))
insert into #tmpValues(ColumnValues)
exec('select CAST ( ( select * from ' + @TableName + ' where YourTablePKColumn = ' + @YourTablePKColumn + '
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) )')
select @values = ColumnValues from #tmpValues
if @QueryTypeID = 0 --insert
set @Query = 'insert into ' + @TableName + '('
else if @QueryTypeID = 1 --update
set @Query = 'update ' + @TableName + ' set '
else if @QueryTypeID = 2 --dalete
set @Query = 'delete ' + @TableName + ' '
select @object_id = object_id from sys.tables where name = @TableName
if not cursor_status('local','columnCursor') <= -1
begin
close columnCursor;
deallocate columnCursor;
end
declare columnCursor cursor local for
select name, user_type_id, convert(nvarchar(10), max_length), is_nullable from sys.columns where object_id = @object_id order by column_id ;
open columnCursor;
fetch next from columnCursor
into @column_name, @user_type_id, @max_length, @is_nullable;
while @@FETCH_STATUS = 0
begin
select @type_name = name, @collation_name = collation_name from sys.types where user_type_id = @user_type_id
if @column_name_id is null
set @column_name_id = @column_name
else
begin
set @column_names += @column_name + ', '
declare @value_keys_start nvarchar(max) = '<' + @column_name + '>', @value_keys_end nvarchar(max) = '</' + @column_name + '>'
if charindex(@value_keys_start,@values,1) = 0
begin
if @QueryTypeID = 0 --insert
set @column_values += 'null,'
else if @QueryTypeID = 1 --update
set @column_values += @column_name + ' = null,'
end
else
begin
if @QueryTypeID = 0 --insert
if @collation_name is null and not (@type_name like '%date%' or @type_name like '%time%')
set @column_values += substring(@values, charindex(@value_keys_start,@values,1) + len(@value_keys_start), charindex(@value_keys_end,@values,1) - (charindex(@value_keys_start,@values,1) + len(@value_keys_start))) + ','
else if @type_name like '%date%' or @type_name like '%time%'
set @column_values += '''' + replace(substring(@values, charindex(@value_keys_start,@values,1) + len(@value_keys_start), charindex(@value_keys_end,@values,1) - (charindex(@value_keys_start,@values,1) + len(@value_keys_start))),'T',' ') + ''','
else
set @column_values += '''' + replace(substring(@values, charindex(@value_keys_start,@values,1) + len(@value_keys_start), charindex(@value_keys_end,@values,1) - (charindex(@value_keys_start,@values,1) + len(@value_keys_start))),'''','''''') + ''','
else if @QueryTypeID = 1 --update
if @collation_name is null and not (@type_name like '%date%' or @type_name like '%time%')
set @column_values += @column_name + '=' + substring(@values, charindex(@value_keys_start,@values,1) + len(@value_keys_start), charindex(@value_keys_end,@values,1) - (charindex(@value_keys_start,@values,1) + len(@value_keys_start))) + ','
else if @type_name like '%date%' or @type_name like '%time%'
set @column_values += @column_name + '=' + '''' + replace(substring(@values, charindex(@value_keys_start,@values,1) + len(@value_keys_start), charindex(@value_keys_end,@values,1) - (charindex(@value_keys_start,@values,1) + len(@value_keys_start))),'T',' ') + ''','
else
set @column_values += @column_name + '=' + '''' + replace(substring(@values, charindex(@value_keys_start,@values,1) + len(@value_keys_start), charindex(@value_keys_end,@values,1) - (charindex(@value_keys_start,@values,1) + len(@value_keys_start))),'''','''''') + ''','
end
end
fetch next from columnCursor
into @column_name, @user_type_id, @max_length, @is_nullable;
end
if not cursor_status('local','columnCursor') <= -1
begin
close columnCursor;
deallocate columnCursor;
end
if @QueryTypeID = 0 --insert
set @Query += substring(@column_names,1,len(@column_names) - 1) + ')
values (' + substring(@column_values,1,len(@column_values) - 1) + ')'
else if @QueryTypeID = 1 --update or delete
set @Query += substring(@column_values,1,len(@column_values) - 1) + ' where YourTablePKColumn = ' + @YourTablePKColumn
else
set @Query += ' where YourTablePKColumn = ' + @YourTablePKColumn
end
Step 3: Created trigger to table you want to have transaction logs
CREATE TRIGGER trg_MyTrigger ON YouTableName
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
SET NOCOUNT ON;
declare @TableName nvarchar(100) = 'YouTableName', @Query nvarchar(max), @QueryTypeID tinyint, @YourTablePKColumn nvarchar(30)
if exists(select * from deleted) and exists(select * from inserted)
begin
set @QueryTypeID = 1
if not cursor_status('local','updatedCursor') <= -1
begin
close updatedCursor;
deallocate updatedCursor;
end
declare updatedCursor cursor local for
select cast(YourTablePKColumn as nvarchar(30)) from inserted;
open updatedCursor;
fetch next from updatedCursor
into @YourTablePKColumn;
while @@FETCH_STATUS = 0
begin
exec dbo.sp_CreateQueryLogs @Query = @Query output, @TableName = @TableName, @YourTablePKColumn = @YourTablePKColumn, @QueryTypeID = @QueryTypeID
insert into TransactionLogs
(Query, DateCreated)
values (@Query,getdate())
fetch next from updatedCursor
into @YourTablePKColumn;
end
if not cursor_status('local','updatedCursor') <= -1
begin
close updatedCursor;
deallocate updatedCursor;
end
end
else if exists(select * from deleted) and not exists(select * from inserted)
begin
set @QueryTypeID = 2
if not cursor_status('local','deletedCursor') <= -1
begin
close deletedCursor;
deallocate deletedCursor;
end
declare deletedCursor cursor local for
select cast(YourTablePKColumn as nvarchar(30)) from deleted;
open deletedCursor;
fetch next from deletedCursor
into @YourTablePKColumn;
while @@FETCH_STATUS = 0
begin
exec dbo.sp_CreateQueryLogs @Query = @Query output, @TableName = @TableName, @YourTablePKColumn = @YourTablePKColumn, @QueryTypeID = @QueryTypeID
insert into TransactionLogs
(Query, DateCreated)
values (@Query,getdate())
fetch next from deletedCursor
into @YourTablePKColumn;
end
if not cursor_status('local','deletedCursor') <= -1
begin
close deletedCursor;
deallocate deletedCursor;
end
end
else
begin
set @QueryTypeID = 0
if not cursor_status('local','insertedCursor') <= -1
begin
close insertedCursor;
deallocate insertedCursor;
end
declare insertedCursor cursor local for
select cast(YourTablePKColumn as nvarchar(30)) from inserted;
open insertedCursor;
fetch next from insertedCursor
into @YourTablePKColumn;
while @@FETCH_STATUS = 0
begin
exec dbo.sp_CreateQueryLogs @Query = @Query output, @TableName = @TableName, @YourTablePKColumn = @YourTablePKColumn, @QueryTypeID = @QueryTypeID
insert into TransactionLogs
(Query, DateCreated)
values (@Query,getdate())
fetch next from insertedCursor
into @YourTablePKColumn;
end
if not cursor_status('local','insertedCursor') <= -1
begin
close insertedCursor;
deallocate insertedCursor;
end
end
END
GO
You would be much better off setting up auditing for this need rather than trying to extract this information retrospectively from the transaction log.
If you are on Enterprise Edition you could use the built in SQL Server Audit functionality, otherwise it should be relative straight forward to log the desired information via triggers.
