See this CP article:
SQL Server: Search Stored Procedure Create/Modify Date or Text using T-SQL[
^]
Prior to 2008 you could use
SELECT * FROM sys.tables
where create_date >= DATEADD(DAY, -9, dateadd(dd, datediff(dd, 0, GETDATE()), 0))
or modify_date >= DATEADD(DAY, -9, dateadd(dd, datediff(dd, 0, GETDATE()), 0))
Potentially you should now use
SELECT *
FROM INFORMATION_SCHEMA.TABLES IS_T
INNER JOIN sysobjects so ON IS_T.[TABLE_NAME] = so.[name]
where crdate >= DATEADD(DAY, -9, dateadd(dd, datediff(dd, 0, GETDATE()), 0))
or refdate >= DATEADD(DAY, -9, dateadd(dd, datediff(dd, 0, GETDATE()), 0))
Caveat - I'm not convinced that
refdate
is actually the last amended date - you will need to research further
Note the stuff I've done with the date - that is so that the comparison is based on the
beginning of the day instead of the time of day that the query is run.