原文:监控SQL：执行表中所有sql语句、记录每个语句运行时间(3)
通过执行一个 带参数的存储过程 exec OpreateTB('OpreateUser','IsRun') 更新表的数据
表结构：（RunTime，RunStatus,BetweenTime,RunLog,IsRun）。
字段解释：
1、RunTime 这条语句的执行时间
2、RunStatus 为执行语句是否成功 1表示执行成功 0 表示异常 NULL 就是还未执行 -1表示回滚
3、BetweenTime ：执行这条语句所用时间
4、RunLog 执行 返回的消息结果 如（1行影响）
5、IsRun ：是否执行语句 0 表示 未执行,1表示已执行
执行过程如果有错误语句直接回滚 并且 Runlog 都更新为'回滚'。
执行 按照 OrderNumber 升序执行语句。
-
--delete from tb_CMd
-
-
CREATE TABLE [dbo].[TB_CMD](
-
[RowGuid] [nvarchar](50) NOT NULL,
-
[RunTime] [date] NULL,
-
[RunStatus] [int] NULL,
-
[SqlEvent] [nvarchar](max) NULL,
-
[OrderNumber] [int] NULL,
-
[IsRun] [bit] NULL,
-
[OpreateUser] [nchar](10) NULL,
-
[SqlType] [nchar](10) NULL,
-
[BetweenTime] [int] NULL,
-
[RunLog] [nvarchar](200) NULL,
-
CONSTRAINT [PK_TB_CMD] PRIMARY KEY CLUSTERED
-
(
-
[RowGuid] ASC
-
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
-
) ON [PRIMARY]
-
-
GO
-
-
ALTER TABLE [dbo].[TB_CMD] ADD CONSTRAINT [DF_TB_CMD_RowGuid] DEFAULT (newid()) FOR [RowGuid]
-
GO
-
-
insert into tb_CMd (sqlevent,OrderNumber,IsRun,OpreateUser )values('create table tb (id int ,name varchar(10))',1,0,'Tom')
-
insert into tb_CMd (sqlevent,OrderNumber,IsRun,OpreateUser )values('insert into tb select 1,''test1''',2,0,'Tom')
-
insert into tb_CMd (sqlevent,OrderNumber,IsRun,OpreateUser )values('insert into tb select 1,''test1''',3,0,'Jack')
-
insert into tb_CMd (sqlevent,OrderNumber,IsRun,OpreateUser )values('insert into tb select 2,''test2''',4,0,'Tom')
-
insert into tb_CMd (sqlevent,OrderNumber,IsRun,OpreateUser )values('update tb set name =''test_1'' where id =1''',5,0,'Tom')
-
insert into tb_CMd (sqlevent,OrderNumber,IsRun,OpreateUser )values('delete from tb where id=1',6,0,'Tom')
-
insert into tb_CMd (sqlevent,OrderNumber,IsRun,OpreateUser )values('select * from#tb',7,0,'Tom')
-
insert into tb_CMd (sqlevent ,OrderNumber,IsRun,OpreateUser )values('drop table tb',8,1,'Tom')
-
go
-
select * from tb_cmd order by OrderNumber asc
-
这里用一个存储过程来实现：
-
create proc dbo.OpreateTB
-
--@OpreateUser nvarchar(100),
-
--@IsRun nvarchar(10)
-
as
-
-
declare @i int
-
declare @start datetime
-
declare @sql nvarchar(max)
-
declare @OrderNumber int
-
declare @error int
-
declare @ROWCOUNT int
-
-
set @i = 1;
-
-
while @i <= (select COUNT(*) from [TB_CMD])
-
begin
-
-
--按照[OrderNumber]进行了排序，每次取出1条
-
;with t
-
as
-
(
-
select *,
-
ROW_NUMBER() over(order by [OrderNumber]) rownum
-
from [TB_CMD]
-
)
-
-
select @sql = [SqlEvent],
-
@OrderNumber = [OrderNumber]
-
from t
-
where rownum = @i
-
-
set @start = GETDATE()
-
-
exec(@sql);
-
-
select @error = @@ERROR,
-
@ROWCOUNT = @@ROWCOUNT
-
-
update [TB_CMD]
-
set BetweenTime = datediff(ms,@start,GETDATE()),
-
RunLog = case when @error = 0 then '（'+cast(@ROWCOUNT as varchar)+'行影响）'
-
else '回滚'
-
end,
-
RunStatus = case when @error = 0 then 1
-
when @error <> 1 then 0
-
end,
-
IsRun = 1
-
where [OrderNumber] = @OrderNumber --这里也修改了
-
-
set @i = @i + 1
-
end
-
-
go
