Last Modified Date of Stored Procedure

[saadabc]

Is there a way in SQL Server to get the Last Modified Date for a stored procedure through query?

 

[gmmastros]

Select * From Information_Schema.Routines

This will show 2 columns at the end. A created date and an altered date. Both values are pulled from the same source and represent the created date.

However, when changing a stored procedure, if you drop iot first and then create it (to effectively alter it), then the created date will work.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[saadabc]

Yeah - the problem is that our developers don't modify Stored Procedures by dropping them and then adding them - they modify them by just double-clicking and modifying the text in enterprise manager -

isn't there another way ?

 

[saadabc]

I'm reading that the Last_Modified Date changing when the Stored Procedure is altered is not implemented in SQL Server 2000 - that it is a feature in SQL Server 2k5 . Is that correct?

Darn - isn't there like a service pack or something that you can install - that adds this feature in SQL 2k as well...

 

[saadabc]

Well - since the last_altered field doesn't do it - I'm going to figure out whether the procedure has changed or not - by getting all the text in the Routine_definition column, storing it in another table - and then comparing it against the value in Routine_definition at a later date.

I'm trying to get the values in the Routine_definition column - but I can't come up with a field size big enough to store it - i set my field to nvarchar(8000) but it still says 'String or data will be truncated'.

What datatype and field size should I set my StoredProcedureText column to ... i can't even look at the definition for the view Information_schemas.Routines because I don't see the view anywhere in Enterprise Manager.



One more thing - I read that the create_date and last_altered columns in the Routines view get their values from the crdate column in sysobjects table - there is another date column in sysobjects called ref_date . what is that column for ?

Thanks.

 

[gmmastros]

Unfortunately, I cannot answer all your questions.

You can use
sp_helptext 'ProcedureName'
to get the text of the stored procedure

To view the definition of Information_Schema.Routines...

use master
go
sp_helptext 'master.Information_Schema.Routines'

I don't know if any of this will help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[saadabc]

Hi
Information_schema.Routines has worked for me as far as getting information about stored procedures - now how do i get the same information for User-Defined Functions....

 

[jbenson001]

You can do:

USE <data base>
Select * from sysobjects
Where xtype = 'FN'

Jim

 

[philhege]

Hi waseemakram,

From BOL:
refdate (datetime) Reserved for future use.

I haven't popped in my SQL2K5 ISO yet, so I can't say for sure if the future is now.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[saadabc]

jbenson's

USE <data base>
Select * from sysobjects
Where xtype = 'FN'


works.

however, i need to get the routine_definition from syscomments. how do i do the join with syscomments....

 

[jbenson001]

Try this:

Select so.[name], sc.text
From sysobjects so
   Inner Join syscomments sc ON so.[id] = sc.[id]
Where so.xtype = 'FN'