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
 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
22403 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