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

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.