SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days

I usually run following script to check if any stored procedure was deployed on live server without proper authorization in last 7 days. If SQL Server suddenly start behaving in un-expectable behavior and if stored procedure were changed recently, following script can be used to check recently modified stored procedure. If stored procedure was created but never modified afterwards modified date and create date for that stored procedure are same.

SELECT
name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7
----Change 7 to any other day value

Following script will provide name of all the stored procedure which were created in last 7 days, they may or may not be modified after that.

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) < 7
----Change 7 to any other day value.

Date condition in above script can be adjusted to retrieve required data.

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

Solarwinds
, ,
Previous Post
SQL SERVER – Result of EXP (Exponential) to the POWER of PI – Functions Explained
Next Post
SQL SERVER – Count Duplicate Records – Rows

Related Posts

84 Comments. Leave new

  • Hi Pinal Dave,

    I have a requirement like trying find the script for alter the Stored procedure and take the backup of existing SP in development server and that should be automated or just script anything is fine.

    Every time if any changes on SP’s in Development Server those data not be reflected in Prod server.

    So is there any other solution for solving the issue.

    Please Help with this.

    Reply
  • The script gives me the date and time the stored procedure was changed, but how do I find the login of who changed the SP

    Reply
  • How to do Undo alter stored procedure?

    Reply
  • Hi Pinal Dave,

    I was wondering is there any possibility to see the content of changed procedure. I’m looking for the old content. Is there a tracking data in the database with that amount of detail.

    Kind regards
    Mirka

    Reply

Leave a Reply

Menu