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://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology | 37 Comments

37 Responses

on July 29, 2007 at 11:26 pm | Reply brembot

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

Hi

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

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

Thanks for correction.
on August 9, 2007 at 4:34 pm | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply Khushbu

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

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

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

thanks , very helpful ^^
on April 28, 2008 at 8:55 pm | Reply 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 | Reply fakhre

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

very nice. I got my doubts cleared through this.thanks a lot
on June 16, 2008 at 6:50 am | Reply 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 | Reply 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 | Reply Bildoss

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

Hi ,

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

Thanks in advance.
on February 4, 2009 at 1:21 pm | Reply Anil

thanks great post

By MK Gandhi
to add srno in retrieved records using querry
on February 7, 2009 at 2:35 am | Reply cyrus m nash

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’
on February 24, 2009 at 5:39 pm | Reply SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days [...]
on April 9, 2009 at 7:11 pm | Reply Ramya

Thank u very much
on April 9, 2009 at 7:12 pm | Reply Ramya

the above information is very helpful. but
I want to list table name also????
Please help me
on April 10, 2009 at 4:40 pm | Reply Tejas Shah

Hi Ramya,

To get tables, modified in last 7 days:

SELECT name
FROM sys.objects
WHERE type = ‘U’
AND DATEDIFF(D,modify_date, GETDATE()) < 7

Thanks,

Tejas
on April 17, 2009 at 8:57 pm | Reply jones

You are one SQL God!

You have helped many people more than any body in the world.

Hope you keep on doing great!!!
on June 17, 2009 at 7:54 pm | Reply Julio Valencia

Thanks for the query, but as an auditor I need to know who made the modification, in other words,
I need know the:
what was modified (object name),
when (when that object was modified)
and the
who (who made the changes).

Is this possible?

Regards

Julio Valencia
on July 3, 2009 at 1:06 pm | Reply Mallik

Hello is there any way to find when the last reindexing was done on the database in sql server 2005?
on July 8, 2009 at 3:27 pm | Reply rajan

I need the query about find modified procedures in sql 2000
on November 9, 2009 at 2:00 am | Reply chas

Thanks dude! Exactly what I was looking for. Saved me hacking this out myself :-)
on November 18, 2009 at 5:20 am | Reply Manish

Hi,

I have run the below procedure in sql 2000 but i am getting the same date in both created and last_alterd column.But user is saying he has modified the Procedure 2 days back.

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’

Kindly help for sql 2000.
on November 20, 2009 at 4:32 pm | Reply kuldeep chanchal

hi pinal

i am new to sql server, and needs to tune one SP.
is there any way to find when any specific database last reindexed?
on November 25, 2009 at 4:12 am | Reply Bangaram

Hi,
this is bangaram, i am new to sql server i have some doubts
1.i have created 1 table and i have to update 1 attribute in that table how.
2.in 1 table first attribute is sl.no it has to be generated number automatically , How can i do this

Please reply fast
on November 26, 2009 at 11:14 am | Reply muthu

My stored procedure was modified by 3rd person,without my knowledge,is it possible? how can i avoid this?
on November 26, 2009 at 4:34 pm | Reply Tejas Shah

Hi Muthu,

You can implement Database Trigger to restrict or log changes.

You will get many blogs to implement that

Thanks,

Tejas
SQLYoga.com
on December 7, 2009 at 3:58 pm | Reply arfan

thx u very much, u really help me