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

  • can you show also the host name of the machine who last modify it?

    Reply
  • Hi

    Is there any way to find out who has modified the stored procdure

    Srinivas

    Reply
  • There’s an error in your queries.

    The line:
    AND DATEDIFF(D,modify_date, GETDATE()) > 7

    should be:
    AND DATEDIFF(D,modify_date, GETDATE()) < 7

    Same with the create_date query. Your query checks modifications/creations MORE than 7 days old.

    Reply
  • Beyond finding out about procs changed, when; how do I tell the last date, value and who modified a database setting such as “Read_Committed_Snapshot”?

    I know I can test to see the current value with “is_read_committed_snapshot_on” from sys.databases but I need to know when the setting was changed and what value/whom set this type of property?

    How much info can I get? how?

    What events in SQL such as Update Statistics could turn this setting off without user intervention or user knowledge? How, separately perhaps, would I also audit such events/changes?

    Thanks

    Reply
  • Thank you for the query…

    I need to add serial number to the record extracted from SQL server 2000. Is there any way to have my own set of serial number generated along with the query.
    ex:
    1 suresh 23
    2 samuel 39
    or

    Please suggest….

    Reply
    • If you want to show data in the front end application, do numbering there. Otherwise copy the resultset a temp table that has identity column

      Reply
  • Thank you for the query…

    I need to add serial number to the record extracted from SQL server 2000. Is there any way to have my own set of serial number generated along with the query.
    ex:
    1 suresh 23
    2 samuel 39
    or

    Please suggest….

    Reply
  • Hi Suresh
    Please try this query

    Select (ROW_NUMBER() OVER ( Order By Height)) as MyRowNumber , Height, CorrectedHeight from Results

    // Gandhi
    Skalar –
    Agaram Instruments
    Chennai

    Reply
  • THATS Good One thanks

    Reply
  • Pankaj Sisodiya
    January 21, 2008 3:35 pm

    Thanks Pinal

    Reply
  • good!

    Reply
  • thanks , very helpful ^^

    Reply
  • is there a query to know the person who has made the change.. ie, if there has been a change in the sp / view / table, how to find who has done that change.

    Reply
  • and what to display tables?

    Reply
  • very nice. I got my doubts cleared through this.thanks a lot

    Reply
  • Thank you so much, viewing the records for the last 7 days was the last task to complete my online application and I just want to say thanks for making it so easy.

    Reply
  • Hi PinalDave,

    i have find an interesting proc on web for some related task that you are doing here, here is location:

    and after that you just call it whit something like:

    use db_name
    execute sp_ListObjects ‘P’

    What do you think about this..?

    Greatings, k

    Reply
  • to list out all the month in a year in Sql Server

    Reply
    • select datename(month,dates) as month_name from
      (
      select dateadd(month,number,0) as dates from master..spt_values where type=’p’ and number between 0 and 11
      ) as t

      Reply
  • Hi ,

    Is there any SQLquery to find out how many tables are created today?

    Thanks in advance.

    Reply
    • select name from sysobjects
      where
      crdate>=dateadd(day,datediff(day,0,getdate()),0) and
      crdate<dateadd(day,datediff(day,0,getdate())+1,0)

      Reply
  • thanks great post

    By MK Gandhi
    to add srno in retrieved records using querry

    Reply
  • do this for sql server 2000 for same result :

    select *
    from information_schema.routines where Routine_Type=’procedure’
    AND DATEDIFF(D,last_altered, GETDATE()) <7
    select @@databasename

    if you want to find modified sp in the specific database
    add the below conditionL

    and specific_Catalog=’yourrdatabasename’

    Reply

Leave a Reply