再发一个比较完整日志回复测试版

[zsforever]

SQL server 2005
定义了char,varchar,datetime,smalldatetime,int,bigint,tinyint,decimal,bit 类型，其它类型会出错
我定义的sp可以打印出最近被删除的sql语句
贴下代码，希望和感兴趣的朋友共享:-)

--Function
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 [dbo].[p_printSql](@sql varchar(max),@flag char(1)=',',@intal varchar(5)='' )
as
declare @l_sql varchar(8000),@i int,@l int
set @l=len(@sql)
while @l>8000-5
begin
select @i=8000-5-charindex(@flag,reverse(left(@sql,8000-5))),@l_sql=left(@sql,@i),@sql=right(@sql,@l-@i),@l=@l-@i
print @intal+@l_sql
end
print @intal+@sql

[zsforever]

--日志SP
alter proc [dbo].[p_getLog](@TableName sysname,@c int=10)
as
set nocount on
declare @s varbinary(8000),@str nvarchar(max),@lb int,@le int,@operation varchar(128),@TSqlColumn nvarchar(max),@TSqlValue nvarchar(max)
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,@bint bigint,@sign binary(1)
declare @sql nvarchar(max)
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,@TSqlColumn='insert into '+@TableName+'(',@TSqlValue='values('
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 @columnname+':'+rtrim(@i)+'->'+rtrim(@length)
if dbo.f_reverseBinary(substring(@s,@TUVLength+@vc/8,1)) & power(2,@vc%8) <> 0
begin
select @TSqlColumn=@TSqlColumn+'['+@columnname+'],',@TSqlValue=@TSqlValue+'NULL,'
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 @columnname+':'+rtrim(@bitAdd)+'->'+rtrim(@length)
end
end
else if @columntype='char'
select @str=@str+@columnname+'='+convert(varchar(256),substring(@s,@i,@length))+','
,@TSqlColumn=@TSqlColumn+'['+@columnname+'],',@TSqlValue=@TSqlValue+''''+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))+','
,@TSqlColumn=@TSqlColumn+'['+@columnname+'],',@TSqlValue=@TSqlValue+''''+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)+','
,@TSqlColumn=@TSqlColumn+'['+@columnname+'],',@TSqlValue=@TSqlValue+''''
+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)+','
,@TSqlColumn=@TSqlColumn+'['+@columnname+'],',@TSqlValue=@TSqlValue+''''
+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))))+','
,@TSqlColumn=@TSqlColumn+'['+@columnname+'],',@TSqlValue=@TSqlValue+rtrim(convert(int,dbo.f_reverseBinary(substring(@s,@i,4))))+','
,@i=@i+4
else if @columntype='bigint'
select @str=@str+@columnname+'='+rtrim(convert(bigint,dbo.f_reverseBinary(substring(@s,@i,8))))+','
,@TSqlColumn=@TSqlColumn+'['+@columnname+'],',@TSqlValue=@TSqlValue+rtrim(convert(bigint,dbo.f_reverseBinary(substring(@s,@i,8))))+','
,@i=@i+8
else if @columntype='tinyint'
select @str=@str+@columnname+'='+rtrim(convert(tinyint,dbo.f_reverseBinary(substring(@s,@i,1))))+','
,@TSqlColumn=@TSqlColumn+'['+@columnname+'],',@TSqlValue=@TSqlValue+rtrim(convert(tinyint,dbo.f_reverseBinary(substring(@s,@i,1))))+','
,@i=@i+1
else if @columntype='decimal'
begin
set @bint=convert(bigint,dbo.f_reverseBinary(substring(@s,@i+1,@length-1)))
select @str=@str+@columnname+'='+case when substring(@s,@i,1) & 1 = 0 then '-' else '' end
+case when len(@bint)<=@scale then '0.'+replicate('0',@scale-len(@bint))+rtrim(@bint)
else left(@bint,len(@bint)-@scale)+'.'+right(@bint,@scale) end+','
,@TSqlColumn=@TSqlColumn+'['+@columnname+'],',@TSqlValue=@TSqlValue+case when substring(@s,@i,1) & 1 = 0 then '-' else '' end
+case when len(@bint)<=@scale then '0.'+replicate('0',@scale-len(@bint))+rtrim(@bint)
else left(@bint,len(@bint)-@scale)+'.'+right(@bint,@scale) end+','
,@i=@i+@length
end
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)))+','
,@TSqlColumn=@TSqlColumn+'['+@columnname+'],',@TSqlValue=@TSqlValue
+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 @columnname+':'+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
select @columnname=name,@length=length,@columntype=typename,@vc=colid-1 from #t where p=2 and pid=@lib
-- print @columnname+':'+rtrim(@ib)+'->'+rtrim(@ie)
if dbo.f_reverseBinary(substring(@s,@TUVLength+@vc/8,1)) & power(2,@vc%8) <> 0
begin
select @str=@str+@columnname+'=NULL,',@TSqlColumn=@TSqlColumn+'['+@columnname+'],',@TSqlValue=@TSqlValue+'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))+','
,@TSqlColumn=@TSqlColumn+'['+@columnname+'],',@TSqlValue=@TSqlValue+''''+convert(varchar(256),substring(@s,@ib,@ie-@ib+1))+''','
select @ib=@ie+1,@i=@i+2
if @count+1<@lenVar
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))+','
,@TSqlColumn=@TSqlColumn+'['+@columnname+'],',@TSqlValue=@TSqlValue+''''+convert(nvarchar(256),substring(@s,@ib,@ie-@ib+1))+''','
select @ib=@ie+1,@i=@i+2
if @count+1<@lenVar
set @ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))
end
set @count=@count+1
set @lib=@lib+1
end

[liangCK]

学习.

[zsforever]

set @str=left(@str,len(@str)-1)
set @TSqlColumn=left(@TSqlColumn,len(@TSqlColumn)-1)+')'
set @TSqlValue=left(@TSqlValue,len(@TSqlValue)-1)+')'
set @sql=@operation+':'+@str
exec dbo.p_printSql @sql,',','--'
if @operation='LOP_DELETE_ROWS'
begin
exec dbo.p_printSql @TSqlColumn
exec dbo.p_printSql @TSqlValue
end
set @lb=@lb+1
end
drop table #t,#t1

--测试
if exists(select * from sysobjects where name='abc')
drop table abc
create table abc(c1 int primary key,c2 datetime,d1 bit,c3 smalldatetime,d3 smalldatetime ,c4 nchar(3),c5 bit,c6 varchar(10),c7 varchar(3),c8 bit,c9 bit,c10 bit,c11 bit ,c12 bit,c13 decimal(38,16),c14 bigint,c15 decimal(19,8))
insert into abc
select -1,getdate(),0,null,getdate()-365,N'213',0,'324afa445','a',null,1,1,0,1,-656.1448,-15555555,1.01
insert into abc
select 2,getdate(),null,getdate(),getdate()-365,N'246',null,'32432asdf','b',0,1,1,0,1,656.1448,34322343,-0.03
insert into abc
select 3,getdate(),1,getdate(),getdate()-365,N'979',0,'anzi3223','c',1,1,1,0,1,-656.1448,45435399,1.04
delete from abc

--运行sp
exec dbo.p_getLog abc,3
--然后把生成的脚本放到窗口里执行，ok，删除的数据又恢复了~

[zsforever]

终于贴完了~

[让你望见影子的墙]

学习

[Zoezs]

学习。

[百年树人]

学习

[htl258_Tony]

学习

[ai_li7758521]

学习

[ks_reny]

谢谢分享。学习。