Wednesday, August 2, 2017

SQL Server - Find Recently Changed Objects

There are times when we need to find out what all objects we made changes to in our database.
This is mostly important when we are developing and making changes on the go in the database, later only to realize that we lost track of what all objects we have changed, thereby loosing some of the changes to be moved to production.

SO here is a query that would give you the list of all objects changed since a certain time. The query could be modified to suit your needs.

Select * 
From sys.objects 
Where (type = 'U' or type = 'P') 
And modify_date > dateadd(d, -30, getdate()) 
Order By modify_date DESC

This query would return "TABLES/PROCEDURES" which have been changed in last 30 DAYS.
There are other similar variants using "m" for MONTHS instead of "d" for days and so on.