sql server 监控多个表的ddl,dml操作

大家好，欢迎来到IT知识分享网。

需求说明：

类似于审计的功能，监控某些表的DDL语句和DML语句并记录下来。 但是不监控select 操作 。以下是脚本。

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))

returns @temptable TABLE (items varchar(8000))

as

begin

declare @idx int

declare @slice varchar(8000)

select @idx = 1

if len(@String)<1 or @String is null return

while @idx!= 0

begin

set @idx =charindex(@Delimiter,@String)

if @idx!=0

set @slice =left(@String,@idx – 1)

else

set @slice = @String

if(len(@slice)>0)

insert into @temptable(items)values(@slice)

set @String =right(@String,len(@String)- @idx)

if len(@String)= 0 break

end

return

end

USE [dbcenter]

GO

/****** Object: StoredProcedure [dbo].[sp_trace_sql_durtion2] Script Date: 2021/1/14 15:10:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROC [dbo].[sp_trace_sql_durtion2]

@tablename nvarchar(500),

@FilePath nvarchar(260),

@onoff int

AS

BEGIN

if @onoff=0

begin

declare @int int

declare yb cursor for

SELECT id FROM sys.traces where path like ‘%’+substring(@FilePath,1,8)+’%’

open yb

fetch next from yb into @int

while @@FETCH_STATUS=0

begin

EXEC sp_trace_setstatus @int,0 –停止, 第一个参数为SELECT * FROM sys.traces中的ID列

EXEC sp_trace_setstatus @int,2 –删除

fetch next from yb into @int

end

close yb

deallocate yb

end

else

begin

DECLARE @rc int,@TraceID int,@MaxFileSize bigint;

SET @MaxFileSize = 50;

declare @date nvarchar(10)

set @date=replace(convert(nvarchar(10),getdate(),120),’-‘,”)

–select @date

set @FilePath=@FilePath+’trc’+@date

declare @filename nvarchar(100)

declare @FilePathnew nvarchar(500)

set @filename=@FilePath+’.trc’

set @FilePathnew=@FilePath

declare @result int =0

–select @filename

execute master.[sys].[xp_fileexist] @filename,@result output

if @result =1

begin

–select ‘aaa’

set @FilePathnew=@FilePath+’_a’

select @FilePathnew

end

EXEC sp_trace_create @TraceID OUTPUT,2,@FilePathnew,@MaxFileSize,NULL;

IF @rc != 0

RETURN;

DECLARE @On bit;

SET @On = 1;

EXEC sp_trace_setevent @TraceID,10,35,@On;

EXEC sp_trace_setevent @TraceID,10,1,@On;

EXEC sp_trace_setevent @TraceID,10,13,@On;

EXEC sp_trace_setevent @TraceID,10,14,@On;

EXEC sp_trace_setevent @TraceID,10,15,@On;

EXEC sp_trace_setevent @TraceID,10,11,@On;

EXEC sp_trace_setevent @TraceID,10,8,@On;

EXEC sp_trace_setevent @TraceID,10,18,@On;

EXEC sp_trace_setevent @TraceID,41,35,@On;

EXEC sp_trace_setevent @TraceID,41,1,@On;

EXEC sp_trace_setevent @TraceID,41,13,@On;

EXEC sp_trace_setevent @TraceID,41,14,@On;

EXEC sp_trace_setevent @TraceID,41,15,@On;

EXEC sp_trace_setevent @TraceID,41,11,@On;

EXEC sp_trace_setevent @TraceID,41,8,@On;

EXEC sp_trace_setevent @TraceID,41,18,@On;

–SET @Seconds = @Seconds * ;

–EXEC sp_trace_setfilter @TraceID,13,0,4,@Seconds;

IF @tablename IS NOT NULL

declare @items nvarchar(500)

declare yb2 cursor for

select items from master.dbo.split(@tablename,’,’)

open yb2

fetch next from yb2 into @items

while @@FETCH_STATUS=0

begin

–EXEC sp_trace_setfilter @TraceID,35,0,0,@DatabaseName

set @items=’%’+@items+’%’

EXEC sp_trace_setfilter @TraceID,1,1,6,@items

–select @items

fetch next from yb2 into @items

end

close yb2

deallocate yb2

EXEC sp_trace_setfilter @TraceID,1,0,7,N’%select%’

EXEC sp_trace_setstatus @TraceID,1

SELECT TraceID = @TraceID;

end

END

exec [sp_trace_sql_durtion2] ‘test,’,’D:\test\’,0 –关闭监控

exec [sp_trace_sql_durtion2] ‘test,test2,test3′,’D:\test\’,’1′ –打开监控

SELECT textdata,cpu,StartTime atime,duration,databasename,* FROM fn_trace_gettable(N’D:\test\trc.trc’,1)

order by StartTime desc