监控SQL：监控SQL的执行情况(3)

需求：

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

--IsRun ：是否执行语句 0 表示 未执行,1表示已执行
----------------------------------

---解释一下本人的需求 

----通过执行一个 带参数的存储过程  exec  OpreateTB(‘OpreateUser‘,‘IsRun‘)   -- 更新这张表的数据  （RunTime，RunStatus,BetweenTime,RunLog,IsRun）

--- RunTime 这条语句的执行时间  

-- RunStatus 为执行语句是否成功  1表示执行成功   0 表示异常  NULL 就是还未执行 -1表示回滚

---BetweenTime ：执行这条语句所用时间

--RunLog 执行 返回的消息结果  如（1行影响）

--执行过程如果有错误语句直接回滚 并且 Runlog 都更新为‘回滚‘

--执行 按照 OrderNumber 升序执行语句

建表语句：

if object_id(‘tb‘) is not null 
    drop table tb
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.  2,‘‘test2‘‘‘,3,0,‘Tom‘)

 

存储过程：

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