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.