以下是建立触发器实现你的需求。 当创建表,或修改表字段 等任何 dll 操作时, 将操作记录到某一个表中.( 基于DDL_DATABASE_LEVEL_EVENTS触发器)然后你在前台 直接查询这个表,去展现了. USE ServerLog --记录的目标库。 -- DROP TABLE [DatabaseLog]; /******************************************* * 需要给所有用户对此表的insert权限. *******************************************/ CREATE TABLE [dbo].[DatabaseLog]( [LogID] BIGINT IDENTITY(1,1) PRIMARY KEY NOT NULL, [ServerName] NVARCHAR(200) NOT NULL, [LoginName] SYSNAME NOT NULL, [DBUser] SYSNAME NOT NULL, DBName NVARCHAR(100) NULL, [EventType] SYSNAME NULL, ObjectType SYSNAME NULL, [Schema] [sysname] NULL, [Object] [sysname] NULL, [TSQL] [nvarchar](max) NOT NULL, [XmlEvent] [xml] NOT NULL, [PostTime] [datetime] NOT NULL, CreateTime DATETIME NOT NULL ) GO
USE [master] GOALTER TRIGGER trg_ServiceEventLog ON ALL SERVER --或者服务器级别 ALL SERVER .数据库:DATABASE FOR DDL_DATABASE_LEVEL_EVENTS --或者服务器级别:DDL_SERVER_LEVEL_EVENTS .数据库级别:DDL_DATABASE_LEVEL_EVENTS AS BEGIN SET NOCOUNT ON; DECLARE @data XML;
SET @data = EVENTDATA(); --PRINT CONVERT(NVARCHAR(max),@data) INSERT INTO ServerLog.[dbo].[DatabaseLog] ([ServerName],[LoginName],[DBUser] ,[DBName],[EventType],[ObjectType],[Schema],[Object],[TSQL],[XmlEvent],CreateTime,[PostTime]) VALUES (
Smo类生成创建表的脚本语句
http://wenku.baidu.com/link?url=Ma_rPOrw2YjxKIMDCB6-Ie3Jnib2M8JTuWI_lbYaBZJSZaoxzjP4GKje91U7QrtTTpzs4uqi0K7OC9vfkX9uNksrV1e6mCwe5aFYA0WJ1e3
以下是建立触发器实现你的需求。
当创建表,或修改表字段 等任何 dll 操作时,
将操作记录到某一个表中.( 基于DDL_DATABASE_LEVEL_EVENTS触发器)然后你在前台 直接查询这个表,去展现了. USE ServerLog --记录的目标库。
-- DROP TABLE [DatabaseLog];
/*******************************************
* 需要给所有用户对此表的insert权限.
*******************************************/
CREATE TABLE [dbo].[DatabaseLog](
[LogID] BIGINT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[ServerName] NVARCHAR(200) NOT NULL,
[LoginName] SYSNAME NOT NULL,
[DBUser] SYSNAME NOT NULL,
DBName NVARCHAR(100) NULL,
[EventType] SYSNAME NULL,
ObjectType SYSNAME NULL,
[Schema] [sysname] NULL,
[Object] [sysname] NULL,
[TSQL] [nvarchar](max) NOT NULL,
[XmlEvent] [xml] NOT NULL,
[PostTime] [datetime] NOT NULL,
CreateTime DATETIME NOT NULL
)
GO
USE [master]
GOALTER TRIGGER trg_ServiceEventLog
ON ALL SERVER --或者服务器级别 ALL SERVER .数据库:DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS --或者服务器级别:DDL_SERVER_LEVEL_EVENTS .数据库级别:DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON; DECLARE @data XML;
SET @data = EVENTDATA();
--PRINT CONVERT(NVARCHAR(max),@data)
INSERT INTO ServerLog.[dbo].[DatabaseLog] ([ServerName],[LoginName],[DBUser]
,[DBName],[EventType],[ObjectType],[Schema],[Object],[TSQL],[XmlEvent],CreateTime,[PostTime])
VALUES
(
@data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(200)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),
@data.value('(/EVENT_INSTANCE/UserName)[1]', 'sysname'),
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'sysname'),
@data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
@data,
@data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
GETDATE()
);
END;GO
ENABLE TRIGGER trg_ServiceEventLog ON ALL SERVER
GO