SQL Server Performance Forum – Threads Archive

schema changes

Does anyone have a script that will find schema changes? I want to create an alert that will send me an email when a user makes a change to the database schema. I really need the script to be able to run on both SQL 2000 and SQL 2005 although I know that is a long shot given the changes made between the versions but it is most important that it runs on SQL 2005. I would be pleased with any scripts, code suggestions, or advice that is offerred. Thanks Michael Herfkens
Herfkens Enterprises, Inc.
[email protected]

You can use DDL triggers… http://msdn2.microsoft.com/en-us/library/ms186406.aspx
http://www.developer.com/db/article.php/3552096
http://www.databasejournal.com/features/mssql/article.php/3581846
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Although they aren’t available in SQL Server 2000. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Is a "more structured" change management approach an option? If so, there are specialized tools out there such as ApexSQL Diff or Idera SQL changemanager, that work with both versions. – Frank Kalis Microsoft SQL Server MVP Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a> Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

I am just wanting an easy way to know when a user has made a change to a table, stored procedure, view, or index. I am aware that I can go look for these items easily enough but I want SQL to send me something as an alert that changes are being made that I need to investigate to ensure they will not break anything I have in place. And I should have been more careful in my wording, Frank. I guess I have already fallen into the SQL 2005 world and it has clouded my verbage. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

In SQL server 2005 you can use trigger to capture the info write to audit table to send alerts but for 2000 I don’t think it is possible. In sql 2000 you can check CRDATE in sysobjects for newly created objects or drop and receated object but objects modified using ALTER statement is not very difficult…you can use Schema_Ver column of sysobjects table but I am not sure how accurate it is…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I’ll give that a try. Thank you Mohammed. Michael Herfkens
Herfkens Enterprises, Inc.
[email protected]

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by mherfkens I am just wanting an easy way to know when a user has made a change to a table, stored procedure, view, or index. I am aware that I can go look for these items easily enough but I want SQL to send me something as an alert that changes are being made that I need to investigate to ensure they will not break anything I have in place. And I should have been more careful in my wording, Frank. I guess I have already fallen into the SQL 2005 world and it has clouded my verbage. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I very much like some of the management tools that are available but for my situation it is not practical. And unfortunately I have too many servers to review each one each day. It looks like the best way may be to utilize triggers, populate an auditing table, and generate a report from the table. Michael Herfkens Herfkens Enterprises, Inc. [email protected] <hr height="1" noshade id="quote"></blockquote id="quote"> I see. Nonetheless I believe that "users making some changes to…" are a pretty good source for trouble. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] SQL Server 2005 introduced some good improvements in this regards. Tracking such changes in SQL Server 2000 is a tough nut. There is a column "schema_ver" in sysobjects that – I guess – was intended for that purpose. However it isn’t reliable, as it depends on the mechanism how you change an object for the value in that column to be increased. If you, for example, want to change a stored procedure and do this by dropping and recreating it, schema_ver will show a 0. If you, however, use the ALTER PROCEDURE way, then schema_ver will show an increased value after the change has been saved. Having that in mind and I see little sense in reinventing the wheel, I think it is a good investment in such a third party tool like the ones I’ve mentioned. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a> Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Michael, I feel your pain. I have one 2000 box that people wanted to track changes to (made by IT) and they were so used to being able to do it in sql2005 it was kind of disappointing for them that I couldnt tell them on that server. sigh. Wish I could of! Michael
MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

You can try the sql trace [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I know it is not optimal way of doing it but when there is only option we have to do it… MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>