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)
84 Comments. Leave new
Thank u very much
the above information is very helpful. but
I want to list table name also????
Please help me
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
Thank you . It is very useful for me.
You are one SQL God!
You have helped many people more than any body in the world.
Hope you keep on doing great!!!
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
Hello is there any way to find when the last reindexing was done on the database in sql server 2005?
I need the query about find modified procedures in sql 2000
There is no way in version 2000 until you have dicumented it
Thanks dude! Exactly what I was looking for. Saved me hacking this out myself :-)
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.
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?
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
If you use version 2005, you can make use of row_number function as shown in this post
My stored procedure was modified by 3rd person,without my knowledge,is it possible? how can i avoid this?
Hi Muthu,
You can implement Database Trigger to restrict or log changes.
You will get many blogs to implement that
Thanks,
Tejas
SQLYoga.com
thx u very much, u really help me
Thank you so very much. Simple script, very usefull
Pinal,
Code work, your scripts have always helped much.
The following to codes are for SQL SVR 2000 version:
Select *
From sysobjects
Where type =’P’
and DateDiff(D,refdate,getdate()) < 7;
Select *
From sysobjects
Where type ='P'
and DateDiff(D,crdate,getdate()) < 7;
Rajan this answers your question.
Thanks
This is just what I was looking for. I want to use it to update on the webserver only the modified stored procedures.
Unfortunately stored procedures that I have just modified with “ALTER PROCEDURE” are still shown as modified many months ago.
By what action is the modified date of a stored procedure updated?
Thanks!
Oops, i reacted bit too quick, it works fine. I ran the SQL on the web server i.s.o. on my laptop. Thanks for the useful codesnippet.
This is how I’ve done this on one of our projects and it works like a charm.
SELECT o.modify_date, o.name, m.definition
FROM sys.sql_modules m
JOIN sys.objects o ON o.object_id = m.object_id
WHERE o.modify_date > DATEADD(week, -1, GETDATE())
If you want to filter out only stored procs change sys.objects to sys.procedures.
Sidenote: there’s a funny thing which caused me a lot of head ache. The field sys.objects.name and the create clause in sys.sql_modules.definition can differ! This happens if you use sp_rename proc.
Sidenote2: If you run ALTER PROC without any actual modifications to the proc, sys.objects.modify_date gets modified still. That is why I check sys.sql_modules.definition field also to prevent false positives.
I hope this helps.
thnaks…..it ‘s very help full 4 my proj.
You are really helpful for us.
How Should I know the relation between a Stored Procedure x Tables it uses?
Is there any way?
Thanks a Lot!
EXEC sp_depends ‘procedure name’