There are few questions which never gets old. Today we are going to one such question. Every single time I go for expert panel in the interview and I end up seeing candidate failing to answer this question about the Stored Procedure modification date. If you look at it, this question is indeed a very important question in development environment and on the production server. I remember an instance when there was a security breach in one of the largest organization where I was consulting, we were able to identify malicious code based on the modified stored procedure date. Though many of you find this question not upto the mark of the interview, I personally like it as I have seen this query in action multiple times.
Question: Write a script to list all the stored procedure modified in last 7 days.
Answer: Here is a simple script when you execute in SQL Server Management Studio, it will give you the name of the stored procedure along with date modified and date created.
SELECT name, modify_date, create_date FROM sys.objects WHERE type = 'P' AND DATEDIFF(D,modify_date, GETDATE()) < 7
Please note that you can change the value 7 to any other value and can get when was the stored procedure last modified. This is one of the script which is evergreen and I have been using this script for quite a while now.
You can also be created and ask the same question which will force the user to re-write a query with different where condition.
Question: Write a script to list all the stored procedures which are never modified since they were created.
Answer: Here is the script which lists all the stored procedures which are never modified since they are created. In this script I have compared modify date with create date to get the list of stored procedures which are never modified.
SELECT name, modify_date, create_date FROM sys.objects WHERE type = 'P' AND modify_date = create_date
Is there any such script do you use in your daily use? Please leave a comment and I will be happy to publish it with due credit.
Reference: Pinal Dave (https://blog.sqlauthority.com)