SQL Tips #7 - Which tables or stored procedures were updated recently

Can't remember which table or stored procedure you just updated, before grabbing a coffee and promptly moving onto the next task on your list?

These queries simply return a list of tables or stored procedures with a modified date being after a certain point/

How it works

The queries return all stored procedures or tables where their modify_date value is after a certain point in time. In this example, we're comparing against the current date minus 7 days (ie a week ago).

The Query

SELECT name,create_date,modify_date FROM sys.procedures
WHERE modify_date > DATEADD(d,-7,GETDATE()) ORDER BY modify_date DESC
SELECT name,create_date,modify_date FROM sys.tables
WHERE modify_date > DATEADD(d,-7,GETDATE()) ORDER BY modify_date DESC

The Output

The query will return results similar to those below.

sp_get_meetings_per_day_list_extended	2017-03-12 08:35:21.217	2017-03-12 08:35:21.217
sp_get_meetings_per_day_extended	2017-03-12 08:35:05.873	2017-03-12 08:35:05.873
tblUsersInvitations	2017-02-20 08:55:56.223	2017-03-10 10:31:08.440
tblUsers	2016-12-08 11:08:42.687	2017-02-28 07:06:38.927
tblMeetingCodes	2016-10-01 12:52:55.220	2017-02-27 13:07:17.637
tblMeetingsFiles	2016-11-17 10:02:39.300	2017-02-27 10:25:32.097

Disclaimer

These queries are provided as a guide, and are by no means perfect. I use these on Microsoft SQL Server 2012, 2014 and 2016.

Tags