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.

List All the Stored Procedure Modified in Last Few Days - Interview Question of the Week #070 SPmodified-800x423

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

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

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

List All the Stored Procedure Modified in Last Few Days - Interview Question of the Week #070 sp-never-modified

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)

, ,
Previous Post
SQL SERVER – Q&A: SQL Clustering Virtual Server Name and Instance Name
Next Post
Primary Key and Null in SQL Server – Interview Question of the Week #071

Related Posts

11 Comments. Leave new

  • Arnab Roy Chowdhury
    May 8, 2016 10:33 am

    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.

    Reply
  • turjachaudhuri
    May 8, 2016 3:00 pm

    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%’

    Reply
  • 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

    Reply
  • Raj Rajaraman
    May 9, 2016 7:08 pm

    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?

    Reply
  • Bernstein S.
    May 10, 2016 7:16 pm

    How is this query done in sql server 2000? I wasn’t able to get the same information from ‘dbo.sysobjects’ table.

    Reply
  • fregatepallada
    May 13, 2016 9:09 am

    I would recommend to use [INFORMATION_SCHEMA].[ROUTINES] system view instead of directly querying sys.objects

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • Dipti Thakurpti
    November 4, 2019 10:30 am

    NICE ARTICLE
    helped me today and always!!!!!

    Reply
  • This there is any way to know the last user executed the stored procedure? Thanks in advance!

    Reply
  • Yes, knowing WHO did the change would be very useful…..

    Reply

Leave a Reply

Menu