This month I would like to share one way, from outside Sage 500,  to find SQL tables that have recently been updated.

Sage 500 application, does have a maintenance Audit Tracking feature that can be activated in a few different modules. That Audit Tracking mechanism tracks inserts, updates, deletes in the respective module area and has reporting to show what was changed and the userid associated with the change.  So for example, if you go to Accounts Payable, Maintenance, AP Setup, Set Up AP Options.   Click the Data Retention tab, and look at the Maintenance Audit frame.  You can select the level to capture for Additions, Changes, and Deletes.   Once selected, the data is stored in a table named tciMaintAuditLog for all the modules.  The Maintenance Audit Log report is located under System Manager, Reports. And as a side note, if you are not concerned with tracking this information but its turned on, you should set the capture level to None.  Slight performance can be gained by setting the capture to None in addition to saving disk space by not storing the records in the tciMaintAuditlog table.

The following information is outside the realm of Sage 500 and because it is related to Microsoft T-SQL is provided with the intention that you are accepting responsibility for use.  The query below makes use of T-SQL DMV(Dynamic Management View) named dm_db_index_usage_stats. and tables view. It shows us the table name and update date if there was a Delete, Insert, or Update performed.

Copy paste, and run the query in a SQL Server Mgmt Studio query window(I ran it from SQL Server v2016) . I will return two columns, the name of the table and date time when it was updated(Updated, meaning an Insert, Update, or Delete was executed)

SELECT object_name(u.object_id), u.last_user_update
FROM   sys.dm_db_index_usage_stats u
       JOIN sys.tables t
         ON t.object_id = u.object_id
WHERE  database_id = db_id() and u.last_user_update is not null
order by u.last_user_update desc

In a test copy of a database, try running the above query and notice the results. Then execute an Insert, Update, and Delete statement against the same or different table.  Re-run the query and review the results.  You should now see a listing of your table object names, and update date.

The one other piece of information that would help is the userid that ran the Insert, Update, or Delete statement.  But the dmv does not hold that information. One possible place to get this would be from SQL Server's default trace. Here is where to find that default trace.

From a query window run the following statement:
 

SELECT path FROM sys.traces WHERE id=1

Say for example the path is 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\log_50.trc', The Insert, Update, and Delete statements are being captured in this trace and can be found in the TextData column. To view the information from this trace file, it would be helpful to insert into a table. I will use a temporary table.   There is also a special function used to access the trace file, fn_trace_gettable.

Here is the next query to run. (You can include additional columns in the select statement, I included  just a few). You will need to replace the path to your default trace below where it shows "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\audittrace20180226001524.trc"

SELECT ApplicationName, LoginName,ServerName, DatabaseName,TextData, StartTime

INTO #results
FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\audittrace20180226001524.trc', default); 

The temp table #results now has the data from the profiler trace. You can now run a query to find any references to the tables listed from the dm_db_index_usage_stats dmv. Or you can look for references both the Insert, Update, or Delete statement and the table name. Example, the following is looking for deletes from tapVendor:

SELECT * FROM #results WHERE textdata LIKE '%delete%tapVendor%'

Now, you would need to correlate the time stamps between the #results table and the dm_db_index_usage_stats dmv records.  You will then know the table that was affected, when it was affected, and the userid that affected the table.

The last two points I want to mention are these. When you are done with the above queries,  execute DROP TABLE #results from query window to delete the temporary table we created. If you created your own,  then delete that table.   Since dmvs do not retain information permanently, there is a chance you will not find this information.  If SQL Server was rebooted after the Insert, Update, or Deletes were executed, the data for the tablename and last_user_update fields will not be there. You can still try to search the default trace, it might be there.  For a more permanent solution, you would need to create a SQL Server audit. I may try to cover that in a future Blog.

I hope you find this information useful. Please give this a try and let me know what you think.