MS SQL Server - How to get the last change date of table data and schema?

Hi Everyone,

Do you need to know when was the last change (Insert, Update or Delete) happened in the SQL Server table data and the table meta data (schema change)?

If you are searching for answers for the above questions, here I am with the answers.

Script to find the last schema change date of a table, you will need to supply table name in the where clause. Run this query in the target database.

SELECT

NAME AS TABLENAME ,

MODIFY_DATE AS LAST_SCHEMA_CHANGE

FROM    SYS.OBJECTS

WHERE   NAME = ‘<TABLE>’

Script to find when the table data was last changed, here you will need to specify the database name and the table number. You may require admin privilege to run this query.

SELECT  

OBJECT_NAME(OBJECT_ID) AS TABLENAME ,

LAST_USER_UPDATE ,

USER_UPDATES ,

INDEX_ID

FROM    SYS.DM_DB_INDEX_USAGE_STATS

WHERE   DATABASE_ID = DB_ID(‘<DATABASENAME>’)

AND OBJECT_ID = OBJECT_ID(‘<TABLENAME>’)

Hope this helped you, Thanks for reading this article !