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

Wednesday, 16 November 2016

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


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


SQL, Programming
Share with: 

Useful SQL tips - how to list all stored procedures or tables updated recently

Support this Site

Popular Articles

What is Kutamo?
Kilimanjaro 2023
Kilimanjaro 2015
Kilimanjaro 2013
Australian Postcodes
New Zealand Postcodes
Worldwide City Database

Recent Articles

Favourite Links

Kutamo Studios
Kilimanjar 2023