Date of last schema change

Hello,
Is there a way to get the date of the last schema change for the database
as a whole? I'm not trying to track individual objects, just if anything,
anywhere, has been changed.
I thought that "select max(crdate) from sysobjects" would work, but crdate
is only the create date, and doesn't change when a SP is altered.
Any ideas would be really appreciated,
Thanks,
MarkNo, SQL Server does not track this information.
http://www.aspfaq.com/2495
SQL Server 2005 will offer DDL triggers, which will allow you to
monitor/track a large portion of the typical object-level changes.
http://www.aspfaq.com/
(Reverse address to reply.)
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:83D61521-55DC-46D4-A400-8D1B78D8704D@.microsoft.com...
> Hello,
> Is there a way to get the date of the last schema change for the
database
> as a whole? I'm not trying to track individual objects, just if anything,
> anywhere, has been changed.
> I thought that "select max(crdate) from sysobjects" would work, but crdate
> is only the create date, and doesn't change when a SP is altered.
> Any ideas would be really appreciated,
> Thanks,
> Mark
>|||Hi Mark,
I noticed that schema_ver does change in Sysobjects for SP (assuming u mean
stored procedures)
schema_ver int Version number that is incremented every time the schema for
a table changes.
http://msdn.microsoft.com/library/d...br />
4zll.asp
Hope this helps,
Best Regards,
Mekim
"Mark" wrote:

> Hello,
> Is there a way to get the date of the last schema change for the databas
e
> as a whole? I'm not trying to track individual objects, just if anything,
> anywhere, has been changed.
> I thought that "select max(crdate) from sysobjects" would work, but crdate
> is only the create date, and doesn't change when a SP is altered.
> Any ideas would be really appreciated,
> Thanks,
> Mark
>|||You can tell that something changed, if you logged all the values yourself
before the change and are comparing afterward. But it tells you nothing
about what was changed or by whom...
http://www.aspfaq.com/
(Reverse address to reply.)
"mekim" <mekim@.discussions.microsoft.com> wrote in message
news:CCED58A7-C1EA-4FC1-92E3-D2D9B65A660C@.microsoft.com...
> Hi Mark,
> I noticed that schema_ver does change in Sysobjects for SP (assuming u
mean
> stored procedures)
> schema_ver int Version number that is incremented every time the schema
for
> a table changes.
>
http://msdn.microsoft.com/library/d..._sys-o_4zll.asp[
color=darkred]
> Hope this helps,
> Best Regards,
> Mekim
>
> "Mark" wrote:
>
database
anything,
crdate|||I think I see it:
select sum(base_schema_ver) from sysobjects
I see that altering a SP updates the base_schema_ver by +16. I assume that
there is no way for the base_schema_ver to decrease. Therefore if we take th
e
sum, and save it to a file, we can then check against this to see if the
database has had any updates.
Does anyone see any errors with this approach?
Thanks!
Mark
"mekim" wrote:
> Hi Mark,
> I noticed that schema_ver does change in Sysobjects for SP (assuming u mea
n
> stored procedures)
> schema_ver int Version number that is incremented every time the schema fo
r
> a table changes.
> http://msdn.microsoft.com/library/d... />
o_4zll.asp
> Hope this helps,
> Best Regards,
> Mekim
>
> "Mark" wrote:
>|||> I see that altering a SP updates the base_schema_ver by +16. I assume that
> there is no way for the base_schema_ver to decrease. Therefore if we take
the
> sum, and save it to a file, we can then check against this to see if the
> database has had any updates.
> Does anyone see any errors with this approach?
Well, I have to ask, what good is the information?
Ooh, there were changes in one or more objects since the last time I polled
the table! You have no idea who did them, or what the changes were. If you
log all objects in your file individually, you might be able to tell which
specific object(s) changed, but you still have no idea how. And the timing
of the change will only be as accurate as the granularity/frequency of your
logging.
This is where PROCESS comes in. Schema/object changes should be channelled
through source control and a deployment process. You should not be going
back in time and "noticing" that somebody changed something on the fly,
especially if your monitoring method has absolutely no concept of how to
revert the change(s)...|||Thanks for the email, good point. I'll need to better investigate the proces
s.
Thanks,
Mark
"Aaron [SQL Server MVP]" wrote:

> the
> Well, I have to ask, what good is the information?
> Ooh, there were changes in one or more objects since the last time I polle
d
> the table! You have no idea who did them, or what the changes were. If y
ou
> log all objects in your file individually, you might be able to tell which
> specific object(s) changed, but you still have no idea how. And the timin
g
> of the change will only be as accurate as the granularity/frequency of you
r
> logging.
> This is where PROCESS comes in. Schema/object changes should be channelle
d
> through source control and a deployment process. You should not be going
> back in time and "noticing" that somebody changed something on the fly,
> especially if your monitoring method has absolutely no concept of how to
> revert the change(s)...
>
>|||Hello Mark.
Check out DB Ghost (http://www.dbghost.com), database change management is
what this software is all about. These are the questions of any database
change management process - why, when, where, who and how - can you answer
all these questions? Also check out this article/white paper on the subject
of automated database change management:
http://www.innovartis.co.uk/pdf/ In...Mgt.
pdf
regards,
Mark Baekdal
http://www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"Mark" wrote:
> Thanks for the email, good point. I'll need to better investigate the proc
ess.
> Thanks,
> Mark
> "Aaron [SQL Server MVP]" wrote:
>