sql server表的操作語句?需求說明：類似于審計的功能，監控某些表的ddl語句和DML語句并記錄下來 但是不監控select 操作 以下是腳本，我來為大家科普一下關于sql server表的操作語句?以下内容希望對你有幫助!

sql server表的操作語句

需求說明：

類似于審計的功能，監控某些表的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 * 1000000;

--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\trc20210114.trc',1)

order by StartTime desc

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!