找回删除的数据

--Function
create FUNCTION dbo.f_splitBinary(@s varbinary(max))
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
GO

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
GO

create PROCEDURE [dbo].[p_printSql](@sql varchar(max),@flag char(1)=',',@intal varchar(5)='' )
as
BEGIN

declare @l_sql varchar(max),
@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

END
GO

 

alter proc [dbo].[p_getLog](@TableName sysname,@c int=10)
AS
BEGIN
set nocount on
declare @s varbinary(max),
@str varchar(max),
@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_DELETE_ROWS')
--AND allocunitname like'%PK_TEMPLOYEESTATION%'
order by [Current LSN] DESC --'LOP_INSERT_ROWS',

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'
BEGIN
--PRINT '@i= ' + CONVERT(NVARCHAR,@i)
--PRINT 'pct: '+convert(nvarchar,convert(int,dbo.f_reverseBinary(substring(@s,@i+4,4))));
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;
END
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
END
GO

转载于:https://www.cnblogs.com/kuailewangzi1212/archive/2013/01/25/2876404.html