Find out the Tables Modified in a SQL Server Database in the Last ‘N’ Days

Here’s a simple SQL Server 2005/2008 T-SQL query that lists the tables of the AdventureWorks database that were modified in the last 30 days:

USE AdventureWorks;
GO
SELECT name AS [TableName],
SCHEMA_NAME(schema_id) AS [Schema],
modify_date as [ModifyDate]
FROM sys.objects
WHERE modify_date > GETDATE() - 30
and type_desc = 'USER_TABLE'
GO

OUTPUT

image

Note: To list all the objects like Constrains, Triggers etc of the AdventureWorks database that were modified in the last 30 days, just remove this line and type_desc = 'USER_TABLE' from the query and execute the query again.


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

2 comments:

gsuttie said...

Is there a way to add this as a column into sql management studio within the object explorer details tab

Anonymous said...

This is not the right. Because ALTER INDEX also changes the Modify Date on the table.