list of stored procedure changes

I noticed that there is an inbuilt report in SSMS for the "Schema Changes
History". My questions are simple - how is it achieved? Is there a system
view I can use to achieve the same? Is there a setting which controls
whether this info is logged? Also, is it possible to get a history of
changes to a particular stored procedure this way, or is it just the name of
the stored procedure?
TIAYou could write your own in SQL server 2005 by using
OBJECT_DEFINITION() and the INFORMATION_SCHEMA.ROUTINES view
Code is here
(http://sqlservercode.blogspot.com/2...e.blogspot.com/
Joe wrote:
> I noticed that there is an inbuilt report in SSMS for the "Schema Changes
> History". My questions are simple - how is it achieved? Is there a system
> view I can use to achieve the same? Is there a setting which controls
> whether this info is logged? Also, is it possible to get a history of
> changes to a particular stored procedure this way, or is it just the name
of
> the stored procedure?
> TIA|||Looks like the Management Studio report is using the traces to look for
object changes, which you can achieve yourself by running a trace.
Alternatively, you could you DDL triggers to track object changes in SQL
Server 2005.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Joe" <Joe.Barrymore@.BT.Com> wrote in message
news:e$2LeF7iGHA.960@.TK2MSFTNGP05.phx.gbl...
I noticed that there is an inbuilt report in SSMS for the "Schema Changes
History". My questions are simple - how is it achieved? Is there a system
view I can use to achieve the same? Is there a setting which controls
whether this info is logged? Also, is it possible to get a history of
changes to a particular stored procedure this way, or is it just the name of
the stored procedure?
TIA|||SQL Server 2005 by default runs a 'lightweight trace' that captures
information about errors, security changes, configuration changes and object
creation, alter and drop. SSMS gets its data from this trace. The trace file
is stored in your \MSSQL\log directory and a new one is created every time
you restart your SQL Server.
A new feature of SQL 2005 that I like a lot is that you can open up a trace
file, or copy its contents to a table for analysis, even while the trace is
running.
I wrote about these traces in the March issue of SQL Server Magazine.
http://www.sqlmag.com/Article/Artic...or_a_Trace.html
There is a configuration option called 'default trace enabled' that turns
this tracing on or off, and it is ON by default.
Hopefully, this can give you a start into doing your own analysis.
HTH
Kalen Delaney, SQL Server MVP
"Joe" <Joe.Barrymore@.BT.Com> wrote in message
news:e$2LeF7iGHA.960@.TK2MSFTNGP05.phx.gbl...
>I noticed that there is an inbuilt report in SSMS for the "Schema Changes
>History". My questions are simple - how is it achieved? Is there a system
>view I can use to achieve the same? Is there a setting which controls
>whether this info is logged? Also, is it possible to get a history of
>changes to a particular stored procedure this way, or is it just the name
>of the stored procedure?
> TIA
>