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

Feed on: Posts; Comments

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

July 10, 2007 by pinaldave

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 (http://www.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, SQL Utility, T SQL, Technology | 20 Comments

20 Responses to “SQL SERVER - 2005 - List All Stored Procedure Modified in Last N Days”

on July 29, 2007 at 11:26 pm brembot

can you show also the host name of the machine who last modify it?
on July 31, 2007 at 11:00 pm Srinivas

Hi

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

Srinivas
on August 3, 2007 at 10:10 am gsnyder

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.
on August 3, 2007 at 10:12 am pinaldave

Thanks for correction.
on August 9, 2007 at 4:34 pm Gord

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
on August 10, 2007 at 7:36 am SQL SERVER - 2005 - Find Stored Procedure Create Date and Modified Date Journey to SQL Authority with Pinal Dave

[...] Aug 10th, 2007 by pinaldave This post is second part of my previous post about SQL SERVER - 2005 - List All Stored Procedure Modified in Last N Days [...]
on August 18, 2007 at 6:53 am Suresh Joe

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….
on September 22, 2007 at 4:52 am suresh 23

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….
on December 6, 2007 at 5:19 pm M.K. Gandhi

Hi Suresh
Please try this query

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

// Gandhi
Skalar -
Agaram Instruments
Chennai
on December 19, 2007 at 11:07 am Khushbu

THATS Good One thanks
on January 21, 2008 at 3:35 pm Pankaj Sisodiya

Thanks Pinal
on March 31, 2008 at 3:25 pm Sakthi Maran

good!
on April 27, 2008 at 5:17 pm 8hitz

thanks , very helpful ^^
on April 28, 2008 at 8:55 pm raghu

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.
on May 8, 2008 at 6:26 pm fakhre

and what to display tables?
on June 12, 2008 at 3:39 pm sivangari

very nice. I got my doubts cleared through this.thanks a lot
on June 16, 2008 at 6:50 am Shanta

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.
on July 4, 2008 at 3:39 pm gorostas

Hi PinalDave,

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

http://searchsqlserver.techtarget.com/searchSQLServer/downloads/5_11_05_Listing_1.txt

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
on July 7, 2008 at 5:04 pm Bildoss

to list out all the month in a year in Sql Server
on November 15, 2008 at 5:54 pm Bhagyesh

Hi ,

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

Thanks in advance.