This post is second part of my previous post about List All Stored Procedure Modified in Last N Days
This post demonstrates the script which displays create date and modify date for any specific stored procedure in SQL Server.
USE AdventureWorks; GO SELECT name, create_date, modify_date FROM sys.objects WHERE type = 'P' AND name = 'uspUpdateEmployeeHireInfo' GO
Now if you run above SP in SQL Server Management studio, you will get following result set. In the result set you can clearly see that it now the query is displaying the name of the stored procedure along with created date as well as modified date. This may be very useful for internal audit as well as if you like to keep watch on your objects.
In recent times at one of the organizations I have noticed that they had created a job to check the stored procedure modification date and if they see changes in the stored procedure date they will create a report based on it. This particular behavior helped the organization to keep track on the object modification and source control.
Leave a comment below this post and tell us if you have seen ever such situation where you have to keep track of the SP modification date?
Reference: Pinal Dave (https://blog.sqlauthority.com)
100 Comments. Leave new
When i am executing the following query
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = ‘P’
it is showing error “Invalid object name ‘sys.objects'”
in sql server 2005, Microsoft SQL Server Management Studio 9.00.1399.00
Well this is excellent, I use this whenever I have to upload new changes to my live site.
Thanks & Regards,
Mayank
please help me
how to find sql objects like stored procedure,functions created client login name in server by using t-sql query
Can we get the Previous version(Before Modification) Script of Modified Stored Procedure in SQL SERVER 2005.
Hi,
i want to find out last access date of any procedure or function can u help me out from this
I would like to capture the date on which a record is being generated
for eg : this is a weekly report and one employee record came in the first week and when i run the report in the next week it should show me the last record running date.
how can we get this since its record level capturing
early response will be appreciated!
do this for sql server 2000 for same result :
—————————————————————————
select *
from information_schema.routines
WHERE
Routine_Type=’procedure’
AND specific_name= ‘uspUpdateEmployeeHireInfo’
AND specific_Catalog=’AdventureWorks’
————————————————————————–
use specific_name for your determind stored procedure name
and specific_Catalog
for your determind database name
good luck :)
hi Navee, what is your database?
hi Raj Acharya, you ca n use above query for this porupose if your databse is sqlserver2000, if you have any other database let me know.
hi sir/madam,
i write one select command in stored procedure
Eg., Select * from employee_tb where employeeid=’50000001′
above statement written in stored procedure.
How to retrive the data in ASP .
i want information employeefirstname ,lastname,badgeid,salary.
how ‘ll you call above storedprocedure in ASP.
finally i want information above 3 fields.
Thanks,
Jagadish
SELECT name as ‘SP Name’
FROM sysobjects
WHERE type = ‘p’
Hi,
I tried this syntax to find the last modified date of a table. But its showing same date for create and modify. Does this modification means change in table structure or modify means modification in data also? I also tried sys.tables to find the modification date but its showing same result? Is there any way to find the last data modification date in a table?
Thanks & Regards
Jitesh
I switched SQL Server versions and forgot how I set up solution/projects which directed my Stored Procedures to my database. Had problems recreated the Solution/project in SQLExpress 2008. When executed individually my stored procedures where dropped into mystery default land. I’ve got a bunch of them there but can’t find them on the machine. Your USE command helped me to insure that I direct my stored procedure to the right database. I know I should take the time to learn how to DBA my SQL Server tool but Ive got enough learning all the .NET stuff righ now. Thanks!
Hi,
I want to keep the history of any updations take place in any stored procedure how is possible in sql2005
Hi,
I tried to add stored procedure in
….\…..\Databases\….\Programmability\Stored Procedures
and it asked to ‘save as’ into a folder but when i refresh the stored procedure i just save won’t show in the Stored Procedure list.
Please help as a I am not a SQL guru.
Thanks,
hi
I want 2 get alter sp name ..
eg:
I was alter a sp in 1 jan 2009 .
I was execute the query
(SELECT * FROM sysobjects WHERE type = ‘P’) it was got all the sp from db.. i want alter sps from jan to till date ..any know tel me
hi
I want 2 get alter sp name ..
eg:
I was alter a sp in 1 jan 2009 .
I was execute the query
(SELECT * FROM sysobjects WHERE type = ‘P’) it was got all the sp from db.. i want alter sps from jan to till date ..
any body knows tel me the answer..
adv tx..
vaira
I would say first this is good for SQL Server 2005. Also you may add ORDER BY modify_date DESC. Sometimes you want to see the latest stored procs.
We have a calendar that we currently have to update the information each day. Is there a way to have the dates in the table update daily? This would make it easier so that we can just add the info in once.
Regards,
Dear pinal
Everyday i face problem regarding upload modified procedure and table script.
i know how to find modified tables and procedure. but its very boring process to generate the modified procedure and table script.
I want tool or query which automatically generate the script of modified procedure and modified tables.
i hope you will get the solution.
Waiting for your reply..
Thanks
Shankar.
Hi Pinal Dave,
Just had a simple question:
I want to place created_date and modified_date field in every(almost all) table of my database and want created_date to be filled when new data is entered and modified_date to be filled when update is made?
can you give me ur suggestion on this?
Thanks
Sabnam