Sometimes we need the information
about data modification report that is
when records of a table had updated or deleted or inserted in a database. Now I'm going
to write a script which will return table name, modification type and
Modification time and total modified rows and modified by:
SELECT
ISNULL( SCHEMA_NAME([schema_id]) + '.','') + ISNULL(OBJECT_NAME(parent_object_id),DBLA.AllocUnitName) AS TableName ,
DBL.[Begin Time] AS ModificationTime,
COUNT(*) Total_Rows_Affected,
MAX(DBL.[Transaction Name]) AS ModificationType,
MAX(SUSER_SNAME(DBL.[Transaction SID])) ModifiedBy
MAX(SUSER_SNAME(DBL.[Transaction SID])) ModifiedBy
FROM Sys.fn_dblog(NULL,NULL)
DBL
INNER JOIN Sys.fn_dblog(NULL,NULL) DBLA
ON LEFT(DBL.[Current LSN],17) = LEFT(DBLA.[Current
LSN],17) LEFT JOIN sys.key_constraints C
ON SCHEMA_NAME([schema_id]) + '.' + OBJECT_NAME(parent_object_id) + '.' + C.name = DBLA.AllocUnitName
WHERE DBL.[Transaction Name] +
'_' + DBLA.Operation IN ('INSERT_LOP_INSERT_ROWS',
'UPDATE_LOP_MODIFY_ROW',
'DELETE_LOP_DELETE_ROWS')
GROUP BY
ISNULL( SCHEMA_NAME([schema_id]) + '.','') + ISNULL(OBJECT_NAME(parent_object_id),
DBLA.AllocUnitName),DBL.[Begin Time]
Sample Output:
TableName
|
ModificationTime
|
Total_Rows_Affected
|
ModificationType
|
dbo.tblP2
|
2014/04/29
22:56:09:287
|
4
|
UPDATE
|
dbo.tblP1
|
2014/04/29
22:54:57:383
|
3
|
UPDATE
|
dbo.tblP1
|
2014/04/29
22:53:04:750
|
1
|
UPDATE
|
dbo.tblP2
|
2014/04/29
23:03:20:810
|
4
|
DELETE
|
dbo.tblP1
|
2014/04/29
22:30:08:947
|
4
|
INSERT
|
dbo.tblP1
|
2014/04/29
23:03:11:407
|
3
|
DELETE
|
dbo.tblP
|
2014/04/29
22:29:08:827
|
4
|
INSERT
|
dbo.tblP1
|
2014/04/29
23:01:33:433
|
1
|
DELETE
|
dbo.tblP
|
2014/04/29
22:26:59:743
|
1
|
INSERT
|
dbo.tblP
|
2014/04/29
22:28:08:623
|
2
|
INSERT
|
dbo.tblP2
|
2014/04/29
22:31:27:680
|
4
|
INSERT
|
dbo.tblP
|
2014/04/29
21:55:30:880
|
1
|
INSERT
|
dbo.tblP
|
2014/04/29
22:27:44:353
|
1
|
INSERT
|
Note: Here meaning of
a. INSERT_LOP_INSERT_ROWS:
To get inserted rows information
b. UPDATE_LOP_MODIFY_ROW:
: To get updated rows information
c. DELETE_LOP_DELETE_ROWS:
: To get deleted rows information
So you can
customize your query by removing above three string in the IN clause.
2 comments:
Hi Mr.Ritesh,
Its a very useful query.
I can able to execute the Query successfully but here am getting the output as null it means after updating/deleting the records also getting the same output.am not able to check the modified records..
please help me out ..
Note :I Just Copied and pasted in Query Window
Regards
Jagadeesh..
This scrip is based on transactional log this cannot be disabled. It can be minimzed by using different recovery model. So I'm not able to guess this issue. Can you confirm me that your not getting difference in output of query:
SELECT *
FROM Sys.fn_dblog(NULL,NULL)
Before and after deleting, inserting and deleting the records.
Post a Comment