解析sql日志

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