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


Wednesday, 16 November 2016

Share with: 
 

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

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

Popular Articles

What is Kutamo?
Kilimanjaro 2015
Exploring Lync and IoT
Exchange 2013 in 60 minutes
Monitoring Lync with MRTG
Lync UCWA Tutorial - Introduction
Tutorial Parts 1 | 2 | 3 | 4 | 5

Recent Articles

Australian Postal Codes
Skype Web SDK
Using the Skype Web SDK from any language or framework
Building a Skype for Business Auto Responder using the Skype Web SDK
Exporting Lync or Skype for Business Contacts with the Skype Web SDK

Favourite Links

Kutamo
Telco Together Foundation
Cloud on Kilimanjaro

Tags / Keywords

SQL, Programming