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)
12 Comments. Leave new
I always use the first script to get a list of stored proc which recently modified. You wrote about this in past and I found that article very useful. Thanks.
I use a script in my daily tasks to find the list of places a particular text is used :
SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE m.definition Like ‘%TextToSearch%’
This website new UI is simply superb.
I typically use the following query to get the last modified stored procedures..
SELECT *
FROM sys.procedures
WHERE DATEDIFF(D,modify_date, GETDATE())>7
ORDER BY modify_date DESC
I have a question. I like to know if any stored procedures not being used or called so that I can cleanup them as we have thousands of SP in a database. Do we have any scripts to find them?
How is this query done in sql server 2000? I wasn’t able to get the same information from ‘dbo.sysobjects’ table.
I would recommend to use [INFORMATION_SCHEMA].[ROUTINES] system view instead of directly querying sys.objects
The modified date is also updated when the SP or function is set to be recompiled the next time it is executed.
(..sp_recompile ”)
Therefore the modified date does not always give you a true reflection of when actual code changes where made to the SP or function. This is unfortunate, since a recompile is sometime required for performance purposes, while it is not an actual code change.
How can we can find last 5 modification history for a particular procedure since sys.objects will show only the one which was modified last.
NICE ARTICLE
helped me today and always!!!!!
This there is any way to know the last user executed the stored procedure? Thanks in advance!
Yes, knowing WHO did the change would be very useful…..
Hi Team,
How can we write trigger on system tables like sys.procedures.
have tried many times but unable to write, getting error invalid for this operation.