see SqlServer Enterprise Manager: the list of stored procedures has just
name, owner, type and create date as columns.
I'd like to have also the date of the last update for a stored procedure.
This can help me a lot.
Is this possible? Is this date recorder in the system tables?
Thanks.Hi,
SQL Server will not store the modified date and time of the procedure.So if
you use ALter Procedure you cant see the modified date and time.
Thanks
Hari
SQL Server MVP
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||Hi
No, SQL Server does not track such info.
Are you only a person in the company to make changes in stored procedures
and other objects?
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
No. This is what source control is for.|||As Aaron mentions â' use a source control, thatâ's what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf)
which outlines the approach Iâ've taken with unparalleled success. Iâ'd love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1. The source code needs to added to your source control.
2. Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why â' and this is just my
opinion â' I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3. Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLLâ's, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4. The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5. You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||As Aaron mentions â' use a source control, thatâ's what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf)
which outlines the approach Iâ've taken with unparalleled success. Iâ'd love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1. The source code needs to added to your source control.
2. Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why â' and this is just my
opinion â' I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3. Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLLâ's, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4. The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5. You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||You can see this in information_schema.routines (column name is last_altered)
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||try testing this column, you should see that it doesn't work...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"harvinder" wrote:
> You can see this in information_schema.routines (column name is last_altered)
> "Francesco" wrote:
> > see SqlServer Enterprise Manager: the list of stored procedures has just
> > name, owner, type and create date as columns.
> > I'd like to have also the date of the last update for a stored procedure.
> > This can help me a lot.
> >
> > Is this possible? Is this date recorder in the system tables?
> > Thanks.
> >|||> You can see this in information_schema.routines (column name is
> last_altered)
NO, the data is not accurate unless you've never modified the stored
procedure. The datetime value stored here is always the same as created
date, and is never updated on ALTER... this will be more reliable using the
catalog views in SQL Server 2005, but for now the data is not stored.
sql
