asked
table modification history.
Hi, I would like to know the detailed history(modification time, username, modified column etc) of table modification like alter. Could you please anyone help me to know the script to find the history of table modifications(at-least last 10 modification). Thanks, Raja.
This site requires that you vote on the answers you've been given. For all helpful answers below, indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution to your problem, indicate this by clicking on the check mark next to that one answer.
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,* FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID( 'Your DB Name') AND OBJECT_ID=OBJECT_ID('Table Name'),
That query will show the last time data modifications have been done, not when structural changes have been made.
There is no mechanism within SQL Server that automatically records that information. You can set up an [Extended Events][1] session to capture it for yourself. You can use the object_altered, object_created and object_created events to audit changes to the data structures of your systems. With that you can tell exactly who changed what. But, you have to set this up, it doesn't exist by default. [1]:
http://technet.microsoft.com/en-us/library/bb630354(v=SQL.105).aspx
Aaron Bertrand published a really useful article here (
http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/) that uses DDL triggers to achieve what you want. We've put it to work here, but make sure you test it really thoroughly first. We found some issues that we had to iron out (set arithabort on I think) that might have been a problem in production.
17 People are following this question.
