LAST_ALTERED Stored Procedure

Hi,
Is there anyway to find out the last altered date of a specific stored
procedure.
I tried Information_Schema.Routines but the value in that column doesn't
change after I modify the stored procedure.
When I look at BOL, it said "The last time the function was modified"
How to find out the date of all last_altered sp then'
Thanks
EdmundYou can't. SQL doesn't store this information.
Drop and recreate the procedure.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:F9223ED0-2791-4CCC-9A6D-6A3BAC640D10@.microsoft.com...
> Hi,
> Is there anyway to find out the last altered date of a specific stored
> procedure.
> I tried Information_Schema.Routines but the value in that column doesn't
> change after I modify the stored procedure.
> When I look at BOL, it said "The last time the function was modified"
> How to find out the date of all last_altered sp then'
> Thanks
> Edmund
>|||what is the LAST_ALTERED column for under Information_Schema.Routines since
I
don't see the value of this column can be changed!!!!
Ed
"Raymond D'Anjou" wrote:

> You can't. SQL doesn't store this information.
> Drop and recreate the procedure.
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:F9223ED0-2791-4CCC-9A6D-6A3BAC640D10@.microsoft.com...
>
>|||These views are defined by ANSI SQL, to they "have to" be present in SQL Ser
ver whether the
information is actually available or not. Read the source for the view and y
ou see that it actually
displays creation date. Books Online is not correct, though, as it states it
is last time altered...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:28240995-CC07-4103-A1F7-6992939B7035@.microsoft.com...
> what is the LAST_ALTERED column for under Information_Schema.Routines sinc
e I
> don't see the value of this column can be changed!!!!
> Ed
> "Raymond D'Anjou" wrote:
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23kFf4e%23WFHA.2124@.TK2MSFTNGP14.phx.gbl...
> These views are defined by ANSI SQL, to they "have to" be present in SQL
> Server whether the information is actually available or not. Read the
> source for the view and you see that it actually displays creation date.
> Books Online is not correct, though, as it states it is last time
> altered...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
Funny.
It's as if I told my client:
I'm obligated to give you a report of your accounts receivables
The amounts are wrong, but you have your report.|||It is probably just a TBD that just fell through the cracks. I once tried to
add a trigger to the sysobjects table that would update the column with
getdate() when a record where xtype='P' is inserted or updated, but this is
not allowed.
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uAQmiv%23WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23kFf4e%23WFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Funny.
> It's as if I told my client:
> I'm obligated to give you a report of your accounts receivables
> The amounts are wrong, but you have your report.
>|||LOL... I agree. It would be better to display NULL and document it properly.
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uAQmiv%23WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23kFf4e%23WFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Funny.
> It's as if I told my client:
> I'm obligated to give you a report of your accounts receivables
> The amounts are wrong, but you have your report.
>|||You DO know that anything, including SELECTs, on System tables are
unsupported.
That's because these tables could change in any upgrade or service pack.
"JT" <someone@.microsoft.com> wrote in message
news:%23jNUtK$WFHA.3140@.TK2MSFTNGP14.phx.gbl...
> It is probably just a TBD that just fell through the cracks. I once tried
> to
> add a trigger to the sysobjects table that would update the column with
> getdate() when a record where xtype='P' is inserted or updated, but this
> is
> not allowed.
> "Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
> news:uAQmiv%23WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> in
>|||Technically, the SELECT against the system tables is supported, as long as y
ou don't derive
information from columns that aren't documented. But the intent if correct,
the structure of the
system tables will change in next version. The will be "replaced" by catalog
views, but still exist
for backwards compatibility and most code will run without changes (assuming
reserved and
non-documented columns has been used).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uXlGE8GXFHA.3584@.TK2MSFTNGP14.phx.gbl...
> You DO know that anything, including SELECTs, on System tables are unsuppo
rted.
> That's because these tables could change in any upgrade or service pack.
> "JT" <someone@.microsoft.com> wrote in message news:%23jNUtK$WFHA.3140@.TK2M
SFTNGP14.phx.gbl...
>|||Thanks Tibor.
I've gone over to the dark side a few times and directly queried system
tables but I've never included these in production code.
Off the top of your head, do you know of any system table information that
cannot be obtained by using "Information schema views" or "System stored
procedures".
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGWA6rHXFHA.2540@.tk2msftngp13.phx.gbl...
> Technically, the SELECT against the system tables is supported, as long as
> you don't derive information from columns that aren't documented. But the
> intent if correct, the structure of the system tables will change in next
> version. The will be "replaced" by catalog views, but still exist for
> backwards compatibility and most code will run without changes (assuming
> reserved and non-documented columns has been used).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
> news:uXlGE8GXFHA.3584@.TK2MSFTNGP14.phx.gbl...
>

Last Identity Value

IDENT_CURRENT returns the last identity value generated for a specific table
in any session and any scope.
@.@.IDENTITY returns the last identity value generated for any table in the
current session, across all scopes.
I need the last identity generated for a specific table for the current
session. Looks like I can do one or the other, but not both. Or is there a
way to get it?It shouldn't be a problem. Within the session scope you just need to
preserve the IDENTITY value after the INSERT to the appropriate table.
In SQL Server 2000 SCOPE_IDENTITY is usually preferred to @.@.IDENTITY
because SCOPE_IDENTITY is unaffected by INSERTs in triggers.
Example:
INSERT INTO table1 (...)
..
SET @.t1 = SCOPE_IDENTITY()
INSERT INTO table2 (...)
..
SET @.t2 = SCOPE_IDENTITY()
David Portas
SQL Server MVP
--|||If the table has a "before insert" trigger on it scope_identity wont work.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130235284.897816.213730@.g14g2000cwa.googlegroups.com...
> It shouldn't be a problem. Within the session scope you just need to
> preserve the IDENTITY value after the INSERT to the appropriate table.
> In SQL Server 2000 SCOPE_IDENTITY is usually preferred to @.@.IDENTITY
> because SCOPE_IDENTITY is unaffected by INSERTs in triggers.
> Example:
> INSERT INTO table1 (...)
> ...
> SET @.t1 = SCOPE_IDENTITY()
>
> INSERT INTO table2 (...)
> ...
> SET @.t2 = SCOPE_IDENTITY()
> --
> David Portas
> SQL Server MVP
> --
>|||If you mean an INSTEAD OF trigger then you are right. Use @.@.IDENTITY in
that instance.
See: http://www.aspfaq.com/show.asp?id=2174
David Portas
SQL Server MVP
--|||Unless you're also dumping data into a Audit table which has an Identity
column.
Then you're up the proverbial ;)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130247300.970289.319770@.g14g2000cwa.googlegroups.com...
> If you mean an INSTEAD OF trigger then you are right. Use @.@.IDENTITY in
> that instance.
> See: http://www.aspfaq.com/show.asp?id=2174
> --
> David Portas
> SQL Server MVP
> --
>|||Then you have to look up the value using the alternate key--which I prefer
because it works with both set-based and single-row inserts. It's also
necessary to issue a select to obtain the current rowversion (timestamp), so
there isn't any additional overhead involved in obtaining the IDENTITY
values (provided you're using optimistic concurrency with rowversioning).
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:435e3382$0$141$7b0f0fd3@.mistral.news.newnet.co.uk...
> Unless you're also dumping data into a Audit table which has an Identity
> column.
> Then you're up the proverbial ;)
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1130247300.970289.319770@.g14g2000cwa.googlegroups.com...
>