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.
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 ServerAmardeep 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.MeanOldDBAderrickleggett@hotmail.comWhen 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.ROUTINESBut 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 descHowever 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).- Goutamquote: Originally posted by Amardeep_2k how to find the date when a stored procedure was last updated in SQL ServerAmardeep 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'GOEXEC dbo.xxxxxxxx_SP_LogScriptRun 'MySProcName', '040504' -- yymmdd formatGOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MySProcName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[MySProcName]GOCREATE PROCEDURE dbo.MySProcName...GOIF exists (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MySProcName]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) GRANT EXECUTE ON dbo.MySProcName TO MyRoleNameGOPRINT '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 |
|
|
|
|
|
|