question

craja26 avatar image
craja26 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.
alter-tablehistory
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
lmr avatar image
lmr answered
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'),
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That query will show the last time data modifications have been done, not when structural changes have been made.
4 Likes 4 ·
Hi LMR, Thanks for the reply. I tried using the above script but still I am not able to get the modified column name and exact user who modified the table. Could you please help me in finding the column and user. Thank you, Raja
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO avatar image
GPO answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.