Track DDL changes across server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Track DDL changes across server

Author

Topic

pithhelmet
Posting Yak Master

183 Posts

Posted - 2008-08-19 : 10:24:26

Hi Everyone -

I need to track all table level schema changes to a server.

The server has several databases, but i don't want to have to create a tracking system per database - i would like it to work on every database in the server.

I need to write the results to an email, and send them to myself when they happen.

if anyone could provide some script that would allow this type of functionality at the server level, that monitors the database level stuff, that would be wonderful!!

SQL Server 2005

thank you

take care
tony

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-19 : 10:26:50

There is schema change report that comes with SQL server. Or you use DDL Trigger to track it and who did it.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-19 : 10:29:51

You can also export to Excel or PDF from Standard Schema change report.

pithhelmet
Posting Yak Master

183 Posts

Posted - 2008-08-20 : 07:34:36

Hi everyone -

Thanks sodeep, i had seen that report, but it fails to meet my requirements in
1) it has to be run interactivily
2) it has to be run on each database
3) it doesn't emal the results

thanks for the info

take care
tony

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-20 : 09:05:25

Then you need to use DDL trigger and send the query output through DBMail.

pithhelmet
Posting Yak Master

183 Posts

Posted - 2008-08-20 : 15:43:32

Hi Sodeep and everyone,

That is the idea, but the problem is (are) the table level changes seem to be done in the database table, this would mean i would need to create a trigger inside of each database that i would like to monitor.

I would like to create a trigger at the sql server level, and track the changes to the tables from that level.

thanks for the reply.

take care
tony

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-20 : 15:58:12

http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources