View “date modified” of all stored procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

View “date modified” of all stored procedures

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2007-08-30 : 15:07:25

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

7423 Posts

Posted - 2007-08-30 : 15:36:37

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

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-30 : 18:08:20

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

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-30 : 21:56:26

Don't know direct way in sql2k.

johnsql
Posting Yak Master

161 Posts

Posted - 2007-09-04 : 13:55:40

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'.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-04 : 14:09:05

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

Kristen
Test

22859 Posts

Posted - 2007-09-04 : 14:36:36

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

johnsql
Posting Yak Master

161 Posts

Posted - 2007-09-05 : 16:33:01

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources