不知道有多少人看到 csdn : jinjazz 写的 日志心得，我近期发现后研究整理了一个 纯sql版的测试版本，大家有兴趣的来, 调用 exec dbo.p_getLog TableName 查看最近的10条log（只是测试用）,有什么提高和指正也请赐教,下面直接贴代码了

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

这个sp对于char,varchar,,int,datetime 的定义没有问题，但bit的处理是有问题的，其实这个东西没啥实际意义，checkpoint后都会失效，专业的数据恢复软件应该不是靠这个函数来得到日志的，但又兴趣的朋友还是可以来尝试下，至少可以更深入理解sqlserver如何存放数据的，下面贴下jinjazz 关于更多数据类型C#里的定义
switch (col_datatype.ToLower())
                {
                    case "char":
                    case "nchar":
                        //读取定长字符串，需要根据表结构指定长度
                        dRow[col_name] = System.Text.Encoding.Default.GetString(data, index, col_length);
                        index += col_length;
                        break;
                    case "datetime":
                        //读取datetime字段，sql为8字节保存
                        System.DateTime date = new DateTime(1900, 1, 1);
                        //前四位1/300秒保存
                        int second = BitConverter.ToInt32(data, index);
                        date = date.AddSeconds(second / 300);
                        index += 4;
                        //后四位1900-1-1的天数
                        int days = BitConverter.ToInt32(data, index);
                        date = date.AddDays(days);
                        index += 4;
                        dRow[col_name] = date;
                        break;
                    case "smalldatetime":
                        //读取smalldatetime字段，sql为4字节保存
                        System.DateTime date1 = new DateTime(1900, 1, 1);
                        //前2位
                        short mm = BitConverter.ToInt16(data, index);
                        date1 = date1.AddMinutes(mm);
                        index += 2;
                        //后2位
                        short dd = BitConverter.ToInt16(data, index);
                        date1 = date1.AddDays(dd);
                        index += 2;
                        dRow[col_name] = date1;
                        break;
                    case "int":
                        //读取int字段,为4个字节保存
                        dRow[col_name] = BitConverter.ToInt32(data, index);
                        index += col_length;
                        break;
                    case "smallint":
                        //读取smallint字段,为2个字节保存
                        dRow[col_name] = BitConverter.ToInt16(data, index);
                        index += col_length;
                        break;
                    case "tinyint":
                        //读取tinyint字段,为1个字节保存
                        dRow[col_name] = data[index];
                        index += col_length;
                        break;
                    case "bigint":
                    case "timestamp":
                        //读取bigint字段,为8个字节保存
                        dRow[col_name] = BitConverter.ToInt64(data, index);
                        index += col_length;
                        break;
                    case "bit":
                        //读取bit字段,为1个字节保存
                        dRow[col_name] = BitConverter.ToBoolean(data, index);
                        index += col_length;
                        break;
                    case "decimal":
                    case "numeric":
                        //读取decimal字段,为?个字节保存
                        //dRow[colName] = BitConverter.ToDouble(data, index);
                        byte[] bDecimal = new byte[col_length];
                        bDecimal[0] = 0;
                        bDecimal[1] = col_xprec;
                        bDecimal[2] = col_xscale;
                        bDecimal[3] = 0;
                        Array.Copy(data, index, bDecimal, 4, col_length - 4);
                        dRow[col_name] = getDecimal(bDecimal, col_xprec, col_xscale);
                        index += col_length;
                        break;
                    case "float":
                        //读取float字段,为4&8个字节保存
                        dRow[col_name] = col_length == 4 ? BitConverter.ToSingle(data, index) : BitConverter.ToDouble(data, index);
                        index += col_length;
                        break;
                    case "money":
                        //读取money字段,为8个字节保存
                        dRow[col_name] = BitConverter.ToDouble(data, index);
                        index += col_length;
                        break;
                    case "smallmoney":
                        //读取smallmoney字段,为4个字节保存
                        dRow[col_name] = BitConverter.ToSingle(data, index);
                        index += col_length;
                        break;
                    case "real":
                        //读取real字段,为4个字节保存
                        dRow[col_name] = BitConverter.ToSingle(data, index);
                        index += col_length;
                        break;
                    case "uniqueidentifier":
                        //读取uniqueidentifier字段,为16个字节保存
                        byte[] bGuid = new byte[col_length];
                        Array.Copy(data, index, bGuid, 0, col_length);
                        dRow[col_name] = new Guid(bGuid);
                        index += col_length;
                        break;
                    default:
                        //忽略不定长字段和其他不支持以及不愿意考虑的字段
                        break;
                }

做了点小的改进
1、考虑字段数超过8个的问题
2、null value的读取

ALTER proc [dbo].[p_getLog](@TableName sysname,@c int=10)
as
set nocount on
declare @s varbinary(8000),@str 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)
declare @TUVLength int,@vc int,@tc int

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 'dbo.'+@TableName+'%'and
    Operation in('LOP_INSERT_ROWS','LOP_DELETE_ROWS') order by [Current LSN] desc

select @TUVLength=sum(length) from #t where p=1
select @tc=count(*) from #t

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='',@vc=0
    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
--        print rtrim(@i)+'->'+rtrim(@length)
        if dbo.f_reverseBinary(substring(@s,4+@TUVLength+2+1,1+((@tc-1)/8))) & power(2,@vc) <> 0
            select @str=@str+@columnname+'=NULL,',@i=@i+@length
        else if @columntype='char'
            select @str=@str+@columnname+'='+convert(varchar(256),substring(@s,@i,@length))+',',@i=@i+@length
        else if @columntype='nchar'
            select @str=@str+@columnname+'='+convert(nvarchar(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='smalldatetime'
            select @str=@str+@columnname+'='+convert(varchar,dateadd(minute,convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))/60
                ,dateadd(day,convert(int,dbo.f_reverseBinary(substring(@s,@i+2,2))),'1900-01-01')),120)+',',@i=@i+4
        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
--        end
        set @vc=@vc+1
        set @lib=@lib+1
    end
    set @i=@i+3+((@tc-1)/8)
    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
--        print rtrim(@ib)+'->'+rtrim(@ie)
        select @columnname=name,@length=length,@columntype=typename from #t where p=2 and pid=@lib
        if dbo.f_reverseBinary(substring(@s,4+@TUVLength+2+1,1+((@tc-1)/8))) & power(2,@vc) <> 0
        begin
            select @str=@str+@columnname+'=NULL,'
            select @ib=@ie+1,@i=@i+2
            set @ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))
        end
        else 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
        else if @columntype='nvarchar'
        begin
            select @str=@str+@columnname+'='+convert(nvarchar(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 @vc=@vc+1
        set @lib=@lib+1
    end
    set @str=left(@str,len(@str)-1)
    print @operation+':'+@str
    set @lb=@lb+1
end
drop table #t,#t1

测试
create table abc(c1 int primary key,c2 datetime,c3 smalldatetime,c4 char(3),c5 varchar,c6 varchar(10),c7 varchar(3),c8 nvarchar,c9 varchar)
insert into abc
    select 1,getdate(),null,'123',0,'324afa445','a',null,1
insert into abc
    select 2,getdate(),getdate(),'456',null,'32432asdf','b',0,1
insert into abc
    select 3,getdate(),getdate(),'789',0,'anzi3223','c',1,1
delete from abc

exec dbo.p_getLog abc

发个最新的,第一范围内的表可以直接解析了
--解决的bit的存储，居然是8个bit存在一个字节上
疑问，系统表的读取可能有问题，表头的数据怎么利用上
不再继续研究了，过程中学到了好多sqlserver存储的细节 ：）
ALTER proc [dbo].[p_getLog](@TableName sysname,@c int=10)
as
set nocount on
declare @s varbinary(8000),@str 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 int

select 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 #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 'dbo.'+@TableName+'%'and
    Operation in('LOP_INSERT_ROWS','LOP_DELETE_ROWS') order by [Current LSN] desc

select @tc=count(*) from #t

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
    set @TUVLength=convert(int,dbo.f_reverseBinary(substring(@s,3,2)))+3
    select @i=5,@str='',@vc=0,@bitCount=0
    select @lib=min(pid),@lie=max(pid) from #t where p=1
    while @lib<=@lie
    begin
        select @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
        begin
            if @columntype<>'bit'
                select @str=@str+@columnname+'=NULL,',@i=@i+@length
            else
            begin
                select @str=@str+@columnname+'=NULL,'
                set @bitAdd = case when @bitCount=0 then @i else @bitAdd end
                set @bitCount = (@bitCount + 1)%8   
                set @i=@i+case @bitCount when 1 then 1 else 0 end
--                print rtrim(@bitAdd)+'->'+rtrim(@length)
            end               
        end
        else if @columntype='char'
            select @str=@str+@columnname+'='+convert(varchar(256),substring(@s,@i,@length))+',',@i=@i+@length
        else if @columntype='nchar'
            select @str=@str+@columnname+'='+convert(nvarchar(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='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+4
        else if @columntype='int'
            select @str=@str+@columnname+'='+rtrim(convert(int,dbo.f_reverseBinary(substring(@s,@i,4))))+',',@i=@i+4
        else if @columntype='decimal'
            select @str=@str+@columnname+'=DECIMAL,',@i=@i+@length
        else if @columntype='bit'
        begin
            set @bitAdd = case when @bitCount=0 then @i else @bitAdd end
            set @bitCount = (@bitCount + 1)%8
            select @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)
        end
        set @lib=@lib+1
    end
    set @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+2
    set @ib=@i + @lenVar*2
    set @ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))
    set @count=0
    select @lib=min(pid),@lie=max(pid) from #t where p=2
    while @lib<=@lie
    begin
--        print rtrim(@ib)+'->'+rtrim(@ie)
        select @columnname=name,@length=length,@columntype=typename,@vc=colid-1 from #t where p=2 and pid=@lib
        if dbo.f_reverseBinary(substring(@s,@TUVLength,1+((@tc-1)/8))) & power(2,@vc) <> 0
        begin
            select @str=@str+@columnname+'=NULL,'
            select @ib=@ie+1,@i=@i+2
            if @count<@lenVar
                set @ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))
        end
        else 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
        else if @columntype='nvarchar'
        begin
            select @str=@str+@columnname+'='+convert(nvarchar(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 @count=@count+1
        set @lib=@lib+1
    end
    set @str=left(@str,len(@str)-1)
    print @operation+':'+@str
    set @lb=@lb+1
end
drop table #t,#t1

学习！！！！！

最好给出个调用这个存储过程的实例，看看效果

后面有个 ”发一个比较完整日志回复测试版 “
可以做测试的，我贴上去的时候系统自动加上了email的字符串，注意去掉既可
我用的是2005版本(sql不同版本存储数据方式是由些不同的)
如果对数据实际是怎么存储的有兴趣，可以研究下，否则没啥用
如果有聚集索引，那个也会不好用(没有处理)，数据定义的类型也不全，对decimal的处理也不完善(太大的数据会出错),check point 后读取也会无效...很多限制
只希望对有兴趣的人有所启迪(自己花了几天时间的研究成果~)

呵呵，有趣的东东。

老7，我在varchar(max)中填充大于8000的字符串，发现bitmap位会出现奇怪的数字，71或者34，按理说应该是00才对。这是不是存储LOB数据时特殊的地方啊？？

SQL自己会不会有一些比较公开的资料，披露这些存储的细节的？