sql server表的操作语句(sqlserver监控多个表的ddl)
需求说明:类似于审计的功能,监控某些表的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
,免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com