SQL varbinary, substring, char to binary,fn_dblog 以及 log 中的 binary 杂记

binary/varbinary 在显示的时候以16进制显示, 比如 "0x010f"

注意显示的结果的位数必定是偶数个 比如"0x11","0x22CC", 不会有 "0x123", 因为varbinary的单位是字节, 而一个字节就是两位的16进制数.

下面两个函数可以把binary/varbinary倒叙排列:

create function [dbo].[f_reverseBinary](@s varbinary(8000))
returns varbinary(128)
as
begin
declare @r varbinary(128)
set @r=0xselect @r=@r+Value from dbo.f_splitBinary(@s) a order by id desc
return @r
endcreate 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
begininsert into @t select substring(@s,@i,1) set @i=@i+1
end
return
end

把字符串转成varbinary的代码:

select CAST('' AS XML).value('xs:hexBinary("02CF")', 'varbinary(max)')

对varbinary/binary的截取可以用substring函数:

select 
substring(
(CAST('' AS XML).value('xs:hexBinary("CD1F")', 'varbinary(max)'))
,1,1)
--0xCD

可以使用上面提到的这些函数对sqlserver的log进行解析, 比如一张表只有一列,类型为int, 进行插入以后可以用fn_dblog函数查询log日志:

SELECT Operation, [RowLog Contents 0]from fn_dblog (null, null) where AllocUnitName like 'dbo.logtest%'andOperation in('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW' )

其中AllocUnitName就是指定表名的地方, 注意后面还要加个通配符%

[RowLog Contents 0]存放着日志, 对于insert操作,  假设你插入了数字666, 你可能得到这样的log:

一段网友的解析语句, 兼容级别为sql2005:

--解析日志
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
begininsert into @t select substring(@s,@i,1) set @i=@i+1
end
return
endGOcreate 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
endGOcreate proc [dbo].[p_getLog](@TableName sysname,@c int=100)
/*
解析日志
胡冰
时间：2010年12月30日
工作单位：www.5173.comsample:  p_getLog 'tablename';*/
as
set nocount on
declare @s varbinary(8000),@s1 varbinary(8000),@str varchar(8000),@str1 varchar(8000),@lb int,@le int,@operation varchar(128)
declare @i int,@lib int,@lie int,@ib int,@ie int,@lenVar int,@columnname sysname,@length int,@columntype varchar(32),@prec int,@scale int
declare @TUVLength int,@vc int,@tc int,@bitAdd int,@bitCount int,@count intselect b.name,b.length,c.name typename,b.colid,b.xprec,b.xscale,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 #tfrom sysobjects a inner join syscolumns b on a.id=b.id inner join systypes c on b.xtype=c.xusertypewhere a.name=@TableName order by b.colidSELECT top(@c) Operation,[RowLog Contents 0],[RowLog Contents 1],[RowLog Contents 2],[RowLog Contents 3],[Log Record],id=identity(int,1,1) into #t1from::fn_dblog (null, null) where AllocUnitName like 'dbo.'+@TableName+'%'andOperation in('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW' )AND Context not in ('LCX_IAM','LCX_PFS')order by [Current LSN] descselect @tc=count(*) from #tselect @lb=min(id),@le=max(id) from #t1
while @lb<=@le
beginselect @operation=Operation,@s=[RowLog Contents 0],@s1=[RowLog Contents 1] from #t1 where id=@lb AND [RowLog Contents 1] IS NOT NULLset @TUVLength=convert(int,dbo.f_reverseBinary(substring(@s,3,2)))+3select @i=5,@str='',@vc=0,@bitCount=0select @lib=min(pid),@lie=max(pid) from #t where p=1 while @lib<=@liebeginselect @columnname=name,@length=length,@columntype=typename,@prec=xprec,@scale=xscale,@vc=colid-1 from #t where p=1 and pid=@lib
--        if @columntype<>'bit'
--            print rtrim(@i)+'->'+rtrim(@length)if dbo.f_reverseBinary(substring(@s,@TUVLength,1+((@tc-1)/8))) & power(2,@vc) <> 0 beginif @columntype<>'bit'select @str=@str+@columnname+'=NULL,',@i=@i+@lengthelsebeginselect @str=@str+@columnname+'=NULL,'set @bitAdd = case when @bitCount=0 then @i else @bitAdd endset @bitCount = (@bitCount + 1)%8    set @i=@i+case @bitCount when 1 then 1 else 0 end
--                print rtrim(@bitAdd)+'->'+rtrim(@length)end                endelse if @columntype='char'select @str=@str+@columnname+'='+convert(varchar(256),substring(@s,@i,@length))+',',@i=@i+@lengthelse if @columntype='nchar'select @str=@str+@columnname+'='+convert(nvarchar(256),substring(@s,@i,@length))+',',@i=@i+@lengthelse 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+8else if @columntype='smalldatetime'select @str=@str+@columnname+'='+convert(varchar,dateadd(minute,convert(int,dbo.f_reverseBinary(substring(@s,@i,2))),dateadd(day,convert(int,dbo.f_reverseBinary(substring(@s,@i+2,2))),'1900-01-01')),120)+',',@i=@i+4else if @columntype='int'select @str=@str+@columnname+'='+rtrim(convert(int,dbo.f_reverseBinary(substring(@s,@i,4))))+',',@i=@i+4else if @columntype='decimal'select @str=@str+@columnname+'=DECIMAL,',@i=@i+@lengthelse if @columntype='bit'beginset @bitAdd = case when @bitCount=0 then @i else @bitAdd endset @bitCount = (@bitCount + 1)%8select @str=@str+@columnname+'='+rtrim(convert(bit,substring(@s,@bitAdd,1)&power(2,case @bitCount when 0 then 8 else @bitCount end-1)))+',',@i=@i+case @bitCount when 1 then 1 else 0 end
--            print rtrim(@bitAdd)+'->'+rtrim(@length)endset @lib=@lib+1endset @i=convert(int,dbo.f_reverseBinary(substring(@s,3,2)))+4+((@tc-1)/8)set @lenVar=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))set @i=@i+2set @ib=@i + @lenVar*2set @ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))set @count=0select @lib=min(pid),@lie=max(pid) from #t where p=2 while @lib<=@liebegin
--        print rtrim(@ib)+'->'+rtrim(@ie)select @columnname=name,@length=length,@columntype=typename,@vc=colid-1 from #t where p=2 and pid=@libif dbo.f_reverseBinary(substring(@s,@TUVLength,1+((@tc-1)/8))) & power(2,@vc) <> 0 beginselect @str=@str+@columnname+'=NULL,'select @ib=@ie+1,@i=@i+2if @count<@lenVarset @ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))endelse if @columntype='varchar'beginselect @str=@str+@columnname+'='+convert(varchar(256),substring(@s,@ib,@ie-@ib+1))+','select @ib=@ie+1,@i=@i+2set @ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))endelse if @columntype='nvarchar'beginselect @str=@str+@columnname+'='+convert(nvarchar(256),substring(@s,@ib,@ie-@ib+1))+','select @ib=@ie+1,@i=@i+2set @ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))endset @count=@count+1set @lib=@lib+1endset @str=left(@str,len(@str)-1)IF @operation ='LOP_MODIFY_ROW'  BEGINset @TUVLength=convert(int,dbo.f_reverseBinary(substring(@s1,3,2)))+3select @i=5,@str1='',@vc=0,@bitCount=0select @lib=min(pid),@lie=max(pid) from #t where p=1 while @lib<=@liebeginselect @columnname=name,@length=length,@columntype=typename,@prec=xprec,@scale=xscale,@vc=colid-1 from #t where p=1 and pid=@lib--        if @columntype<>'bit'--            print rtrim(@i)+'->'+rtrim(@length)if dbo.f_reverseBinary(substring(@s1,@TUVLength,1+((@tc-1)/8))) & power(2,@vc) <> 0 beginif @columntype<>'bit'select @str1=@str1+@columnname+'=NULL,',@i=@i+@lengthelsebeginselect @str1=@str1+@columnname+'=NULL,'set @bitAdd = case when @bitCount=0 then @i else @bitAdd endset @bitCount = (@bitCount + 1)%8    set @i=@i+case @bitCount when 1 then 1 else 0 end--                print rtrim(@bitAdd)+'->'+rtrim(@length)end                endelse if @columntype='char'select @str1=@str1+@columnname+'='+convert(varchar(256),substring(@s1,@i,@length))+',',@i=@i+@lengthelse if @columntype='nchar'select @str1=@str1+@columnname+'='+convert(nvarchar(256),substring(@s1,@i,@length))+',',@i=@i+@lengthelse if @columntype='datetime'select @str1=@str1+@columnname+'='+convert(varchar,dateadd(second,convert(int,dbo.f_reverseBinary(substring(@s1,@i,4)))/300,dateadd(day,convert(int,dbo.f_reverseBinary(substring(@s1,@i+4,4))),'1900-01-01')),120)+',',@i=@i+8else if @columntype='smalldatetime'select @str1=@str1+@columnname+'='+convert(varchar,dateadd(minute,convert(int,dbo.f_reverseBinary(substring(@s1,@i,2))),dateadd(day,convert(int,dbo.f_reverseBinary(substring(@s1,@i+2,2))),'1900-01-01')),120)+',',@i=@i+4else if @columntype='int'select @str1=@str1+@columnname+'='+rtrim(convert(int,dbo.f_reverseBinary(substring(@s1,@i,4))))+',',@i=@i+4else if @columntype='decimal'select @str1=@str1+@columnname+'=DECIMAL,',@i=@i+@lengthelse if @columntype='bit'beginset @bitAdd = case when @bitCount=0 then @i else @bitAdd endset @bitCount = (@bitCount + 1)%8select @str1=@str1+@columnname+'='+rtrim(convert(bit,substring(@s1,@bitAdd,1)&power(2,case @bitCount when 0 then 8 else @bitCount end-1)))+',',@i=@i+case @bitCount when 1 then 1 else 0 end--            print rtrim(@bitAdd)+'->'+rtrim(@length)endset @lib=@lib+1endset @i=convert(int,dbo.f_reverseBinary(substring(@s1,3,2)))+4+((@tc-1)/8)set @lenVar=convert(int,dbo.f_reverseBinary(substring(@s1,@i,2)))set @i=@i+2set @ib=@i + @lenVar*2set @ie=convert(int,dbo.f_reverseBinary(substring(@s1,@i,2)))set @count=0select @lib=min(pid),@lie=max(pid) from #t where p=2 while @lib<=@liebegin--        print rtrim(@ib)+'->'+rtrim(@ie)select @columnname=name,@length=length,@columntype=typename,@vc=colid-1 from #t where p=2 and pid=@libif dbo.f_reverseBinary(substring(@s1,@TUVLength,1+((@tc-1)/8))) & power(2,@vc) <> 0 beginselect @str1=@str1+@columnname+'=NULL,'select @ib=@ie+1,@i=@i+2if @count<@lenVarset @ie=convert(int,dbo.f_reverseBinary(substring(@s1,@i,2)))endelse if @columntype='varchar'beginselect @str1=@str1+@columnname+'='+convert(varchar(256),substring(@s1,@ib,@ie-@ib+1))+','select @ib=@ie+1,@i=@i+2set @ie=convert(int,dbo.f_reverseBinary(substring(@s1,@i,2)))endelse if @columntype='nvarchar'beginselect @str1=@str1+@columnname+'='+convert(nvarchar(256),substring(@s1,@ib,@ie-@ib+1))+','select @ib=@ie+1,@i=@i+2set @ie=convert(int,dbo.f_reverseBinary(substring(@s1,@i,2)))endset @count=@count+1set @lib=@lib+1endset @str1=left(@str1,len(@str1)-1)ENDIF @operation ='LOP_MODIFY_ROW'  BEGINprint   @operation+'修改前值： '+@strprint   @operation+'修改后值：'+@str1ENDELSEBEGINprint @operation+':'+@strENDset @lb=@lb+1
ENDdrop table #t,#t1GO