This post is second part of my previous post about SQL SERVER – 2005 – 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
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
i ran this command and it says -invalid column -created_date and invalid modified_date.
also sys.objects is sysobjects.
pls let me know the correct syntax to find modified date of SP
I don’t know about the first poster, but I ran this command just fine. Copy & paste…replaced the stored procedure name and the information came up just fine.
@Jake,
I agree with you.
I think first poster might be talking about SQL Server 2000. As mentioned in post title it is for only SQL Server 2005.
@radheshyam patel,
The syntax is correct for SQL Server 2005. I only provide correct syntax. Also sys.objects is correct syntax for SQL Server 2005.
Regards,
Pinal Dave (http://www.SQLAuthority.com)
I need to take the complete modification history of a stored procedure. Sysobjects hold only last modified date. But i need history of Stored procedure every time i modifies.
can u give me a solution.
Hi Dave,
I am using SQL2005 Management studio express and try to find the select * from sys.objects then it displays Invalid object name ‘SYS.OBJECTS’.
As I have to find the last modified date of any stored procedure i ma using the query:
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = ‘P’ AND name = ’sp_name’
if I use sysobjects then there is no field as modify_date. there are two date fields crdate and refdate but both are same. even if I modify the procdure its values remains the same as crdate. Please suggest. its urgent. u can also tell me for SQL 2000.
Hi Yoganand,
Any updates. How did you get the complete modification history of a Stored procedure.
Rashmi
Hi…This will gonna work for SQL Server 2000
USE pubs
GO
SELECT name, crdate, refdate
FROM sysobjects
WHERE type = ‘P’ AND name = ‘insert_authors’
GO
I wanted to select only the procedures in a database. How can I write the select statement?
is refdate is modified date in sqlserver2000
Hi,
Would like to ask if in SQL Enterprise manager, I modified the table, will the “Create Date” time stamp change? What resources can I read regarding this?
thanks
Hey.
In Oracle it is possible to see what stored procedures are valid by issueing a “compile” command. Is there any similar way in MS SQL Server? I think of the situation where a stored procedure is doing a SELECT on a column that later changes its name and hence all of a sudden will fail.
I hope there is.
Cheers
Claus
Hi,
Yes, it shows same date but i have made changes in stored procedure.
Salamat po!
Dear Sir…
can i know the stored proc for altering date.(current system date)
Hi,
I think we can not able to identify the modified date of a procedure in sqlserver2000. we need to use tools to identify them. but in sqlserver2005 we can do by using the script given by dave.
Hi,
I want to write a stored procedure for knowing the server date(Current date). so please can you help me out regarding this, please.
Thanks,
gowtham
my database is having windows authentication i need to know who modified my stored procedure
i m not getting a right output.
i want a procedure which returns a seach value a any name given to it from a table
Hi all,
Im using two database db1,db2, the data will be insert,update will done in db1 will be uploaded once in a day.if i updated or inserted the records in another day means it should fetch that records alone and updated to db2 database,im using sql server 2000,asp.net2.0.how can i acheive this one.
Regrds
Kanna
Thanks for the query! Worked great!
thank u alot for that code
it work just fine but i need to know when was the last modify date for the data in my database
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
[...] SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date [...]
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
ALTER PROCEDURE setuserlogin
(
@username varchar (50),
@logintime datetime
)
AS
UPDATE userprofile
set logintime=@logintime,onlinestatus=1
WHERE
username=@username;
if i exe this then 1 error is coming near ste userlogin
please suggest something,so i can exe it.