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

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.

</p>
<p style="text-align: justify;">SELECT name, modify_date, create_date
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7</p>
<p style="text-align: justify;">

Solarwinds

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

</p>
<p style="text-align: justify;">SELECT name, modify_date, create_date
FROM sys.objects
WHERE type = 'P'
AND modify_date = create_date</p>
<p style="text-align: justify;">

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.

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

Solarwinds
,
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

9 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

Leave a Reply

Menu