SQL: Get list of latest modified objects

In this blog I will show how to get the list of latest modified objects in a database with SQL or you can say the recently updated objects. Yesterday I was in a need where I have to find the recently modified items in a database, I got it done with a query by using sys.objects table. Below I have written three queries to achieve it. It will give you the list of modified tables, stored procedures , functions etc.

SELECT  name ,
        type ,
        type_desc ,
        create_date ,
        modify_date
FROM    sys.objects
WHERE   type <> 's'
ORDER BY modify_date DESC




-----------------------



DECLARE @Date DATETIME
SET @Date = '2009-09-03'

SELECT  name ,
        type ,
        type_desc ,
        create_date ,
        modify_date
FROM    sys.objects
WHERE   type <> 's'
        AND modify_date >= DATEADD(dd, DATEDIFF(dd, 0, @Date), 0) --Midnight on the Start date
ORDER BY modify_date DESC





----------------------



DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '2009-09-03'
SET @EndDate = '2010-09-03'

SELECT  name ,
        type ,
        type_desc ,
        create_date ,
        modify_date
FROM    sys.objects
WHERE   type <> 's'
        AND modify_date >= DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) --Midnight on the Start date
        AND modify_date < DATEADD(dd, DATEDIFF(dd, 0, @EndDate + 1), 0) --Midnight of the day after End date
ORDER BY modify_date DESC

The first one is the simple one which will give the list of all modified objects in descending order, the second one is to get the records which are modified after a particular date and the third query is to get the modified records falling between the range of two dates.

Do let me know your feedback, comments.