监控SQL：通过SQL Server的DDL触发器来监控数据库结构的变化(1)

如果你要同步不同数据库之间的数据，首先会想到的是数据库复制技术，但如果让你同步数据库的结构，你会想到什么呢？

下面是一个例子，用来说明通过sql server中的DDL触发器，来记录create table、alter table、drop table等ddl操作。

--2.1在master数据库中建立服务器级别跟踪表use mastergocreate table server_eventdata(eventdata xml,     principal_user nvarchar(100),     login_user nvarchar(100)    )go    /*select * from sys.trigger_event_typeswhere type_name like '%table%' or      type_name like '%deny%' or      type_name like '%revoke%'*/        --2.2建立服务器级别触发器create trigger gyy_serveron all serverfor create_table,    alter_table,    drop_tableasinsert into server_eventdataselect EVENTDATA(),USER,SUSER_NAME()go--2.3create database wcgouse wcgocreate table dbo.wc_table(v int)insert into dbo.wc_table values(1)gouse wcgoalter table dbo.wc_tableadd name varchar(100)alter table dbo.wc_tableadd tt datetime default getdate()go--查看记录的事件select --EVENTDATA,  --里面是所有的信息       eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)') as '事件类型',       eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)') as 'sql语句',              eventdata.value('(/EVENT_INSTANCE/PostTime)[1]','datetime') as '执行时间'              /*       '登录名' + eventdata.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(100)') +        '用户名' + eventdata.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(100)') + ',授予者'+       eventdata.value('(/EVENT_INSTANCE/Grantor)[1]','nvarchar(100)') + ' 把类型为：' +       eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)') + '的对象' +       eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)') + '.' +       eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '.' +       eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '的' +       eventdata.value('(/EVENT_INSTANCE/Permissions/Permission)[1]','nvarchar(100)') +'权限授予给' +              eventdata.value('(/EVENT_INSTANCE/Grantees)[1]','nvarchar(100)')                   */            from master.dbo.server_eventdata t/*事件类型         sql语句                                                    执行时间                 (无列名)CREATE_TABLEcreate table dbo.wc_table(v int)                         2013-11-07 10:22:31.157NULLALTER_TABLE    alter table dbo.wc_table add name varchar(100)          2013-11-07 10:23:59.987NULLALTER_TABLE    alter table dbo.wc_table add tt datetime default getdate() 2013-11-07 10:24:00.013NULL*/