List All the Stored Procedure Modified in Last Few Days – Interview Question of the Week #070

There are few questions that never get old. Today we are going to one such question. Every single time I go for the expert panel in the interview and I end up seeing candidates 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 the development environment and on the production server. I remember an instance when there was a security breach in one of the largest organizations 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 up to 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 procedures modified in the 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 the 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 scripts 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 conditions.

Question: Write a script to list all the stored procedures which are never modified since they were created.

Answer: Here is the script that 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. You can always reach out to me on Twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)