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 HerfkensHerfkens 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=’
![Wink ;) ;)](styles/default/xenforo/smilies/wink.png)
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=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
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"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by mherfkens</i><br /><br />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=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
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=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
]]>