Apr 29, 2014

Script to know updated, inserted or deleted records from tables in sql server: Last Table Data Modification

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

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:

  1. 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..


    ReplyDelete
    Replies
    1. 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.

      Delete