jinjazz 日志测试 sql版本

create function dbo.f_splitBinary(@s varbinary(8000))

returns @t table(id int identity(1,1),Value binary(1))

as

begin

declare @i int,@im int

select @i=1,@im=datalength(@s)

while @i<=@im

begin

  insert into @t select substring(@s,@i,1) 

  set @i=@i+1

end

return

end

create function dbo.f_reverseBinary(@s varbinary(128))

returns varbinary(128)

as

begin

declare @r varbinary(128)

set @r=0x

select @r=@r+Value from dbo.f_splitBinary(@s) a order by id desc

return @r

end

create proc p_getLog(@TableName sysname,@c int=10)

as

set nocount on

declare @s varbinary(8000),@str varchar(max),@lb int,@le int,@operation varchar(128)

declare @i int,@lib int,@lie int,@ib int,@ie int,@lenVar int,@f bit,@cv int,@columnname sysname,@length int,@columntype varchar(32)

select b.name,b.length,c.name typename,b.colid,

case when c.name not like '%var%' and c.name not in ('xml','text','image') then 1 else 2 end p,row_number() over(partition by 

case when c.name not like '%var%' and c.name not in ('xml','text','image') then 1 else 2 end order by colid) pid

into #t

from sysobjects a inner join syscolumns b on a.id=b.id inner join systypes c on b.xtype=c.xusertype

where a.name=@TableName order by b.colid

SELECT top(@c) Operation,[RowLog Contents 0],id=identity(int,1,1) into #t1

from::fn_dblog (null, null) 

where AllocUnitName like [email=]'dbo.'+@TableName+'%'and[/email]

Operation in('LOP_INSERT_ROWS','LOP_DELETE_ROWS') order by [Current LSN] desc

select @lb=min(id),@le=max(id) from #t1

while @lb<=@le

begin

select @operation=Operation,@s=[RowLog Contents 0] from #t1 where id=@lb

select @i=5,@str='',@f=0,@cv=1

select @lib=min(pid),@lie=max(pid) from #t where p=1 

while @lib<=@lie

begin

  select @columnname=name,@length=length,@columntype=typename from #t where p=1 and pid=@lib

  if @columntype='char'

  select @str=@str+@columnname+'='+convert(varchar(256),substring(@s,@i,@length))+',',@i=@i+@length

  else if @columntype='datetime'

  select @str=@str+@columnname+'='+convert(varchar,dateadd(second,convert(int,dbo.f_reverseBinary(substring(@s,@i,4)))/300

    ,dateadd(day,convert(int,dbo.f_reverseBinary(substring(@s,@i+4,4))),'1900-01-01')),120)+',',@i=@i+8

  else if @columntype='int'

  select @str=@str+@columnname+'='+rtrim(convert(int,dbo.f_reverseBinary(substring(@s,@i,4))))+',',@i=@i+4

  else if @columntype='bit'

  begin

  select @str=@str+@columnname+'='+rtrim(convert(bit,substring(@s,@i,1)))+',',@i=@i+1

  if @f=0 set @f=1 else set @f=0

  end

  set @lib=@lib+1

end

if @f=0 set @i=@i+3 else set @i=@i+2

set @lenVar=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))

set @i=@i+2

set @ib=@i + @lenVar*2

set @ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))

select @lib=min(pid),@lie=max(pid) from #t where p=2 

while @lib<=@lie

begin

  select @columnname=name,@length=length,@columntype=typename from #t where p=2 and pid=@lib

  if @columntype='varchar'

  begin

  select @str=@str+@columnname+'='+convert(varchar(256),substring(@s,@ib,@ie-@ib+1))+','

  select @ib=@ie+1,@i=@i+2

  set @ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))

  end

  set @lib=@lib+1

end

set @str=left(@str,len(@str)-1)

print @operation+':'+@str

set @lb=@lb+1

end

drop table #t,#t1