SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 View “date modified” of all stored procedures
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

johnsql
Posting Yak Master

USA
161 Posts

Posted - 08/30/2007 :  15:07:25  Show Profile  Reply with Quote
Experts,

I’d like to know whether there is some way in SQL Server 2000 (using script or GUIs) to view “date modified” of all stored procedures or some sp in a database. Please show me how I can do that.
Thanks in advance.
johnsql

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 08/30/2007 :  15:36:37  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
http://weblogs.sqlteam.com/joew/archive/2007/08/29/60312.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/30/2007 :  18:08:20  Show Profile  Reply with Quote
quote:
Originally posted by jsmith8858

http://weblogs.sqlteam.com/joew/archive/2007/08/29/60312.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS



OP asked about SQL Server 2000; sys.procedures is only available in SQL Server 2005.




CODO ERGO SUM
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 08/30/2007 :  21:56:26  Show Profile  Reply with Quote
Don't know direct way in sql2k.
Go to Top of Page

johnsql
Posting Yak Master

USA
161 Posts

Posted - 09/04/2007 :  13:55:40  Show Profile  Reply with Quote
quote:
Originally posted by jsmith8858

http://weblogs.sqlteam.com/joew/archive/2007/08/29/60312.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS




I am using SQL Server 2000, and the error when running the system sp the site shows. The sp seems th be in SLQ Server 2005, doesn't it? Is there some way to know that info with SQL Seever 2000 I am using?

SELECT [name] ,modify_date ,create_date , *FROM sys.proceduresspecified cannot be found.Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.procedures'.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/04/2007 :  14:09:05  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
As MVJ points out, that is only available in SQL 2005, a point which I missed. As far as I know, unfortunately, there is no way to do this in SQL 2000.

- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 09/04/2007 14:13:27
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/04/2007 :  14:36:36  Show Profile  Reply with Quote
If you DROP/CREATE the Sproc then the CRDATE will be set (in the sysobjects table).

If you use ALTER then there is no record of when it was changed.

We put a "logging statement" in our Sproc scripts something like:

--
PRINT 'Create procedure MySProc'
GO
EXEC MyLogginSProc 'MySProc', '070904'
go
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[MySProc]') ... )
	DROP PROCEDURE dbo.MySProc
GO
CREATE PROCEDURE dbo.MySProc
...

which logs the time the Sproc was created, and the version, which we use to review when, and which version, things happened.

Kristen
Go to Top of Page

johnsql
Posting Yak Master

USA
161 Posts

Posted - 09/05/2007 :  16:33:01  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

If you DROP/CREATE the Sproc then the CRDATE will be set (in the sysobjects table).

If you use ALTER then there is no record of when it was changed.

We put a "logging statement" in our Sproc scripts something like:

--
PRINT 'Create procedure MySProc'
GO
EXEC MyLogginSProc 'MySProc', '070904'
go
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[MySProc]') ... )
	DROP PROCEDURE dbo.MySProc
GO
CREATE PROCEDURE dbo.MySProc
...

which logs the time the Sproc was created, and the version, which we use to review when, and which version, things happened.

Kristen



Kristen,
So this is a workaround. Thanks for your post.
johnsql
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000