how to find the date when a stored procedure was l

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

Old Forums

CLOSED - General SQL Server

how to find the date when a stored procedure was l

Author

Topic

Amardeep_2k
Starting Member

1 Post

Posted - 2004-04-07 : 04:47:11

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

Amardeep Singh

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-07 : 06:52:43

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.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-07 : 20:25:20

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 [url]http://weblogs.sqlteam.com/markc[/url]

goutam
Starting Member

1 Post

Posted - 2004-05-04 : 09:10:09

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

Kristen
Test

22859 Posts

Posted - 2004-05-04 : 14:24:31

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

MuadDBA

628 Posts

Posted - 2004-05-04 : 14:40:03

Awesome idea.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-04 : 17:05:12

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources