监控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