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)

SQL DateTime, SQL Scripts, SQL Stored Procedure
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 everyone ,
    how can i list out the table names from the stored procedure in sql 2005….,

    Reply
  • Use this

    exec sp_depends ‘procedure name’

    Reply
  • Thank you for for the tip, it is useful when we make the new release.

    Reply
  • thanks…

    Reply
  • friendlyvlad
    May 23, 2011 9:13 am

    Thank you! It is a great post. I’d like to clarify one thing. Both queries can return the same values. This can happen if I created a new stored procedure within the last 7 days and then updated it.

    Reply
  • Thanks for your comments. in several oportunities they were very usefull for me.

    Thanks from Colombia – South América

    Reply
  • Hi Pinal

    I need to generate the list of all tables whose structures were modified in the last 15 days. All tables whose data were modified should not be included in the list.

    Reply
  • Hi.. Thanks for all the info.. I have a query in my environment.. please let me know hw to achieve the below agenda :

    -> Monitor the environment and find if a new sql agent  job has been created. If yes report the details of the job; We can catch the new jobs and update the job with notifications assigned to person/DL responsible for the job.

    -> Monitors the environment and find status of Server level priviliges of all the logins and sends a report. We can  identify any changes in the priviliges of logins by comparing with the previous report

    Please help me.. Its urgent..

    Reply
  • Hi Pinal,

    Is there a way to find out who (which domain user) has created a login on the server? And who (which domain user) has modified the same login ? if they have modified, what have they edited, old value and new values to be specific ?

    select * from master.sys.syslogins — shows the createdate, updatedate and accdate but not the user info.

    sys.fn_tracegettable also has a limitation of only 5 traces.

    Can you help me ?

    Nitin.

    Reply
  • I need to build an sql of all users who have logged the last three months. I’ve trying to understand system sps for example whoisactive, but I dont undertand them. Any idea please.

    Reply
  • thanks a lot

    Reply
  • How to see who changed sprocs?

    Reply
  • Thank You So much.. Its a timely help for my project

    Reply
  • i need program for dispaly the date*dayname of the one week using sql procedure can u help me

    Reply
  • Thank you so much.. Helped

    Reply
  • how to compare multiple date attributes with current date

    Reply
  • We are trying to create a stored procedure to archive data older than 6 months (180 days) from our production database in to a new archive database.

    We also want to delete those archived rows from the production database.

    We are thinking to include a while loop, but we want to archive only 1,000 rows a day and we need to schedule it on daily basis.

    Can you please share us your experience – ASAP

    Thanks

    Reply
  • Hi Pinal,
    Daily above 1000 records insert into production database, when i scheduled job, i want 6 months older than records go to archive table. remaining records are existing in production table. currently we are maintain 6 months records only its enough. other records are we are maintain archive table.

    Thanks

    Reply
  • Run on all databases
    DECLARE @command varchar (1000)
    SELECT @command = ‘use ? SELECT ”?” DBname,name,modify_date
    FROM sys.objects
    WHERE type = ”P”
    AND DATEDIFF(D,modify_date, GETDATE()) < 150'
    EXEC sp_MSforeachdb @command

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

Leave a Reply