ALTER PROCEDURE [dbo].[Sp_RecoveryDate](@TableName varchar(100))
AS
Begin
DECLARE @RowLogcontent0 VARBINARY(max) --日志
DECLARE @Fixedlengthsize VARBINARY(2) --固定数据长度
DECLARE @FixedLengthData VARBINARY(max) --固定长度数据
DECLARE @TotalNoofColumns VARBINARY(4) --总列数
DECLARE @TotalCol INT --总列数
DECLARE @NullBytes VARBINARY(100) --NULL Bitmap
DECLARE @NullBitmaplength INT --Null Bitmap位数
DECLARE @Totalnoofvariablecolumns INT --可变列数
DECLARE @Columnoffsetarray VARBINARY(max) --可变列位置数组
DECLARE @VariableColumnStart VARBINARY(max) --可变数据
DECLARE @BitValue VARBINARY(100) =0x
DECLARE @Weizhi INT
DECLARE @BitLength INT=0
DECLARE @guding INT
DECLARE @Record INT
DECLARE @id INT=1
IF OBJECT_ID(@TableName) IS NULL
BEGIN
RAISERROR('表不存在',16,1)
RETURN
End
CREATE TABLE #Rst
(
Rowid INT,
ColName VARCHAR(100),
Value nVARCHAR(max)
)
CREATE TABLE #Var
(
num INT,
Value VARBINARY(max)
)
SELECT A.name AS CName,A.column_id,B.name AS TName,A.max_length,A.system_type_id,A.precision,A.scale,
CASE WHEN B.name IN('varchar','nvarchar','varbinary') THEN 0 ELSE A.max_length END AS Lh,CONVERT(Varbinary(max),null) AS Val,0 cNull INTO #Tmp
FROM sys.columns A INNER JOIN sys.types B ON B.user_type_id = A.user_type_id
WHERE object_id=OBJECT_ID(@TableName)
IF EXISTS(SELECT 1 FROM #Tmp WHERE TName IN ('datetimeoffset','datetime2','text','ntext','image','CURSOR','hierarchyid','sql_variant','XML','table'))
BEGIN
DROP TABLE #Tmp
RAISERROR('存在不能解析的数据类型',16,1)
RETURN
END
SELECT [RowLog Contents 0] As RowLogcontent0,ROW_NUMBER() OVER(ORDER BY [Transaction ID]) AS id INTO #Data
FROM sys.fn_dblog(NULL,null)
WHERE PARSENAME(AllocUnitName,1)=PARSENAME(@TableName,1) AND Context='LCX_HEAP' AND Operation='LOP_DELETE_ROWS'
AND SUBSTRING([RowLog Contents 0],1,1) IN(0x10,0x30,0x70)
SET @Record=@@ROWCOUNT
WHILE @id<=@Record
BEGIN
;
WITH T
AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY column_id) AS id FROM #Tmp WHERE TName='bit'
)
Update T SET lh=CASE WHEN id%8=1 THEN 1 ELSE 0 END
SELECT @RowLogcontent0=RowLogcontent0 FROM #Data WHERE id=@id
--前面2字节固定
SET @Weizhi=3
SET @Fixedlengthsize= Substring (@RowLogcontent0, @Weizhi,2)
SET @Weizhi=@Weizhi+2
SELECT @guding=SUM(Lh) FROM #Tmp
--取固定长度字段(N字节)
SET @FixedLengthData = Substring (@RowLogcontent0, @Weizhi,@guding)
SET @Weizhi=@Weizhi+@guding
DECLARE @I INT =1
DECLARE @H INT=0
SET @BitValue=0x
;
WITH T
AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY column_id) AS id FROM #Tmp WHERE Lh>0
)
Update T SET @I=@I+Lh,@H=Lh,Val=SUBSTRING(@FixedLengthData,@I-@H,Lh),@BitValue=CASE WHEN TName='bit' AND lh=1
THEN @BitValue+SUBSTRING(@FixedLengthData,@I-@H,Lh) ELSE @BitValue END
SELECT @BitLength=SUM(lh) FROM #Tmp WHERE TName='bit'
--更新bit位
UPDATE #Tmp SET Val=0x0 WHERE TName='bit'
;
WITH T
AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY column_id) AS id FROM #Tmp WHERE TName='bit'
)
Update T SET val=1
WHERE id IN(SELECT * FROM ft_GetNullBitMap(@BitValue,@BitLength*8))
--总列数(2字节)
set @TotalNoofColumns= Substring (@RowLogcontent0, @Weizhi,2)
SET @Weizhi=@Weizhi+2
SET @TotalCol=dbo.fn_ConvertToInt(@TotalNoofColumns)
--NULL Bitmap
SET @NullBitmaplength=Ceiling (CONVERT(INT,@TotalCol)/8.0)
SET @NullBytes = Substring (@RowLogcontent0, @Weizhi, @NullBitmaplength )
SET @Weizhi=@Weizhi+@NullBitmaplength
--可变列数(2字节)
Set @Totalnoofvariablecolumns = dbo.fn_ConvertToInt(SUBSTRING (@RowLogcontent0,@Weizhi ,2))
SET @Weizhi=@Weizhi+2
--可变列偏移量(@Totalnoofvariablecolumns*2)
SET @Columnoffsetarray=SUBSTRING(@RowLogcontent0, @Weizhi ,@Totalnoofvariablecolumns*2)
SET @Weizhi=@Weizhi+@Totalnoofvariablecolumns*2
--可变数据
DECLARE @X int
SELECT @X=COUNT(1) FROM #Tmp WHERE Lh>0
SET @VariableColumnStart=SUBSTRING(@RowLogcontent0, @Weizhi ,DATALENGTH(@RowLogcontent0))
--计算NullBitMaps
;WITH T
AS
(
SELECT * FROM ft_GetNullBitMap(@NullBytes,@NullBitmaplength*8)
)
UPDATE #Tmp SET cNull=1
WHERE column_id IN(SELECT * FROM T)
DECLARE @J INT =1
DECLARE @L INT =0
DECLARE @K INT =1
DECLARE @M INT=@Totalnoofvariablecolumns
DECLARE @A VARBINARY(10)
DECLARE @B VARBINARY(10)
DECLARE @ColumnoffsetarrayBak VARBINARY(max)=@Columnoffsetarray
SET @Columnoffsetarray=0x
SET @I=1
WHILE @I<=@Totalnoofvariablecolumns
BEGIN
SET @Columnoffsetarray=SUBSTRING(@ColumnoffsetarrayBak,@K,2)+@Columnoffsetarray
SET @I=@I+1
SET @K=@K+2
END
SET @I=1
SET @K=DATALENGTH(@VariableColumnStart)
WHILE @I<=@Totalnoofvariablecolumns
BEGIN
SET @B=SUBSTRING(@Columnoffsetarray,@J+2,2)
SET @A=SUBSTRING(@Columnoffsetarray,@J,2)
SET @L=(dbo.fn_ConvertToInt(@A)-dbo.fn_ConvertToInt(@B))
INSERT INTO #Var VALUES(@M,SUBSTRING(@VariableColumnStart,@K-@L+1,@L))
SET @K=@K-@L
SET @J=@J+2
SET @I=@I+1
SET @M=@M-1
END
;WITH T
AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY column_id ) AS id FROM #Tmp WHERE lh=0 AND TName<>'bit'
)
UPDATE T SET Val=Value
FROM #Var
WHERE T.id=num
INSERT INTO #Rst ( Rowid , ColName , Value )
SELECT @ID,CName,CASE WHEN cNull=1 THEN NULL ELSE
CASE
WHEN TName='smalldatetime' THEN CONVERT(VARCHAR(40),dbo.fn_ConvertToSmallDateTime(Val),121)
WHEN TName='datetime' THEN CONVERT(VARCHAR(40),dbo.fn_CONVERTtodatetime(Val),121)
WHEN TName='date' THEN CONVERT(VARCHAR(40),dbo.fn_ConvertToDate(Val),121)
WHEN TName='time' THEN CONVERT(VARCHAR(40),dbo.fn_ConvertToTime(Val),121)
WHEN TName='char' THEN dbo.fn_CONVERTtochar(Val)
WHEN TName='nchar' THEN dbo.fn_CONVERTtonchar(Val)
WHEN TName='nvarchar' THEN dbo.fn_CONVERTtonvarchar(Val)
WHEN TName='varchar' THEN dbo.fn_CONVERTtovarchar(Val)
WHEN TName='real' THEN CONVERT(NVARCHAR(100),dbo.Fn_ConvertToSingle(Val))
WHEN TName='float' THEN CONVERT(NVARCHAR(100),dbo.Fn_ConvertToDouble(Val))
WHEN TName='bigint' THEN CONVERT(NVARCHAR(100),dbo.fn_ConvertTobigInt(Val))
WHEN TName='int' THEN CONVERT(NVARCHAR(100),dbo.fn_ConvertToInt(Val))
WHEN TName='smallInt' THEN CONVERT(NVARCHAR(100),dbo.fn_ConvertTosmallInt(Val))
WHEN TName='smallmoney' THEN CONVERT(NVARCHAR(100),dbo.fn_ConvertToSmallMoney(Val))
WHEN TName='tinyint' THEN CONVERT(NVARCHAR(100),dbo.fn_ConvertTotinyint(Val))
WHEN TName='money' THEN CONVERT(NVARCHAR(100),dbo.fn_ConvertToMoney(Val))
WHEN TName='bit' THEN CONVERT(NVARCHAR(100),dbo.fn_ConvertTobit(Val))
WHEN Tname='Numeric' THEN dbo.fn_ConvertToNumeric(Val,precision,scale)
WHEN Tname='Decimal' THEN dbo.fn_ConvertToDECIMAL(Val,precision,scale)
WHEN Tname='uniqueidentifier' THEN CONVERT(NVARCHAR(100),dbo.fn_ConvertTouniqueidentifier(Val))
WHEN Tname='binary' THEN sys.fn_varbintohexstr(val)
WHEN Tname='varbinary' THEN sys.fn_varbintohexstr(val)
WHEN Tname='timestamp' THEN sys.fn_varbintohexstr(val)
END
END
FROM #Tmp
UPDATE #Tmp SET Val=NULL,cNull=0
TRUNCATE TABLE #Var
SET @id=@id+1
END
DECLARE @Sql VARCHAR(8000)=''
DECLARE @Filed VARCHAR(4000)=''
SELECT @Filed=@Filed+',['+CName+']' FROM #Tmp
SET @Filed=STUFF(@Filed,1,1,'')
SET @Sql='Select Rowid,'+@Filed+ ' FROM #Rst
PIVOT
(
MAX(value)
FOR
ColName IN ('+@Filed+')
) P'
EXEC(@sql)
DROP TABLE #Data
DROP TABLE #Tmp
DROP TABLE #Var
END