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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 Old Forums
 CLOSED - General SQL Server
 how to find the date when a stored procedure was l
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Amardeep_2k
Starting Member

India
1 Posts

Posted - 04/07/2004 :  04:47:11  Show Profile
how to find the date when a stored procedure was last updated in SQL Server

Amardeep Singh

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 04/07/2004 :  06:52:43  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
You can't. You have to use SourceSafe or something like that for this process. The only way you can tell is if you always use drop/create instead of alter on the procedures. It then changes the date of the procedure.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 04/07/2004 :  20:25:20  Show Profile  Visit AjarnMark's Homepage
Try

SELECT ROUTINE_NAME, LAST_ALTERED FROM INFORMATION_SCHEMA.ROUTINES

But my preference is to do what Derrick says, and script it all out doing Drop/Create instead of ALTER.

--------------------------------------------------------------
Find more words of wisdom at http://weblogs.sqlteam.com/markc
Go to Top of Page

goutam
Starting Member

1 Posts

Posted - 05/04/2004 :  09:10:09  Show Profile
select ROUTINE_TYPE,ROUTINE_NAME,LAST_ALTERED from INFORMATION_SCHEMA.ROUTINES order by LAST_ALTERED desc
However there is a bug in SQL Server , the last altered date does not change in Standard editions of SQL Server. (It might be fixed in some SP's not sure).
- Goutam


quote:
Originally posted by Amardeep_2k

how to find the date when a stored procedure was last updated in SQL Server

Amardeep Singh

Go to Top of Page

Kristen
Test

United Kingdom
22858 Posts

Posted - 05/04/2004 :  14:24:31  Show Profile
quote:
how to find the date when a stored procedure was last updated in SQL Server

All our SProcs are stored in individual files on a local server (i.e. rather than doign RightClick PROPERTIES in Enterprise Manager or using ObjectBrowser in Query Analyser to create a script (from the SProc already in the DB).

All the script files are in the style of:

PRINT 'Create procedure MySProcName'
GO
EXEC dbo.xxxxxxxx_SP_LogScriptRun 'MySProcName', '040504'   -- yymmdd format
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MySProcName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MySProcName]
GO

CREATE PROCEDURE dbo.MySProcName
...
GO
IF exists (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MySProcName]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	GRANT EXECUTE ON dbo.MySProcName TO MyRoleName
GO
PRINT 'Create procedure MySProcName DONE'
GO

The xxxxxxxx_SP_LogScriptRun SProc does an INSERT into a table recording the Date/Time the SProc was run, and also the "version" number - we use a "yymmdd" style number for that, and we update this when something "significant" changes.

We can then compare the Script Run Log Table on the Development database with the Production/Live database to find out what has NOT been run on Production!

Kristen
Go to Top of Page

MuadDBA
Aged Yak Warrior

USA
628 Posts

Posted - 05/04/2004 :  14:40:03  Show Profile
Awesome idea.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/04/2004 :  17:05:12  Show Profile  Visit nr's Homepage
Have a look at
http://www.nigelrivett.net/SQLServerReleaseControl.htm

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000