Is it possible to know which program ran some SQL command and the table related to the command?

Question

How to make a query that returns the history of the executed command type (UPDATE, INSERT, DELETE), the date, the name of the program you executed (Program_name), and the table that has been modified?

Example:

1 | Name of Table A| UPDATE | 07/07/2020 | programaX.exe

2 | Name of Table B| INSERT | 07/07/2020 | programY.exe

3 | Name of Table A| INSERT | 05/07/2020 | programZ.exe

Answer 1

Solution found, with option to perform grouping by transaction type.

SELECT 
    objectName,
    begintime,
    endtime,
    account,
    LOP_INSERT_ROWS [Insert],
    LOP_MODIFY_ROW [Update],
    LOP_DELETE_ROWS [Delete],
    --SUM(LOP_INSERT_ROWS) [Insert],
    --SUM(LOP_MODIFY_ROW) [Update],
    --SUM(LOP_DELETE_ROWS) [Delete],
    transactionID,
    prog_name
FROM (  SELECT object_name(p.object_id) objectName, tlog.[Current LSN], T.begintime begintime, c.endTime endtime, tlog.Operation operation, T.account account, T.tranID transactionID, L.program_name as prog_name  
        FROM sys.objects so
        inner join sys.partitions p on p.object_id=so.object_id
        inner join sys.system_internals_allocation_units AU on p.partition_id=AU.container_id
        inner join (select [Current LSN], [transaction ID] tranID,[end time] endTime, AllocUnitId, operation, Context
                    from ::fn_dbLog(null, null)
                    where (operation in ('LOP_INSERT_ROWS', 'LOP_MODIFY_ROW', 'LOP_DELETE_ROWS')))tlog on tlog.AllocUnitId=AU.allocation_unit_id
        inner join (select [Transaction ID] tranID, [End Time] endTime
                    from ::fn_dbLog(null, null)
                    where Operation = 'LOP_COMMIT_XACT' and [End Time]>=DateADD(mi, -1500, Current_TimeStamp)) c on tlog.tranID = c.tranID
        inner join (SELECT [Transaction ID] tranID, SUSER_SNAME ([Transaction SID]) AS account, [Begin Time] as begintime
                    FROM fn_dblog (NULL, NULL)
                    WHERE [Operation] = N'LOP_BEGIN_XACT') T on tlog.tranID = T.tranID
        inner join (SELECT DBLOG.[Transaction ID], DBSYS.program_name
                    FROM fn_dblog(NULL, NULL) DBLOG
                    JOIN sys.dm_exec_sessions DBSYS ON DBLOG.SPID = DBSYS.session_id
                    WHERE program_name IS NOT NULL) L on L.[Transaction ID] = T.tranID

        WHERE so.type='U' ) X 
        pivot ( COUNT(operation) FOR operation in (LOP_INSERT_ROWS, LOP_MODIFY_ROW,LOP_DELETE_ROWS,LOP_ABORT_XACT,LOP_COMMIT_XACT)) p
    
--GROUP BY objectName, begintime, endtime, account, p.transactionID, p.prog_name
ORDER BY begintime DESC