In all my years of working SQL server, one of the most commonly asked questions has always been “How can we recover deleted record?”

Now, it is very easy to recover deleted data from your SQL server 2005 or above.

How to generate Insert statements from table data using SQL Server »

How to recover deleted data from SQL Server

October 22, 2011 by Muhammad Imran

In all my years of working SQL server, one of the most commonly asked questions has always been “How can we recover deleted record?”

Now, it is very easy to recover deleted data from your SQL server 2005 or above.(Note: This script can recover following data types & compatible with CS collation).

• image
• text
• uniqueidentifier
• tinyint
• smallint
• int
• smalldatetime
• real
• money
• datetime
• float
• sql_variant
• ntext
• bit
• decimal
• numeric
• smallmoney
• bigint
• varbinary
• varchar
• binary
• char
• timestamp
• nvarchar
• nchar
• xml
• sysname

Let me explain this issue demonstrating simple example.

Download Stored Procedure :

Now, you need to create the procedure to recover your deleted data

Explanation:

How does it work? Let’s go through it step by step. The process requires seven easy steps:

Step-1:

We need to get the deleted records from sql server. By using the standard SQL Server function fn_blog, we can easily get all transaction log (Including deleted data. But, we need only the selected deleted records from the transaction log. So we included three filters (Context, Operation & AllocUnitName).

• Context (‘LCX_MARK_AS_GHOST’and ‘LCX_HEAP’)
• Operation (‘LOP_DELETE_ROWS’)
• AllocUnitName(‘dbo.Student’) –- Schema + table Name

Here is the code snippet:

Select [RowLog Contents 0] FROM sys.fn_dblog(NULL,NULL)WHEREAllocUnitName =‘dbo.Student’ AND Context IN (‘LCX_MARK_AS_GHOST’,‘LCX_HEAP’) AND Operation in (‘LOP_DELETE_ROWS’)

This query will return number of columns providing different information, but we only need to select the column “RowLog content o, to get the deleted data.

The Column “RowLog content 0″ will look like this:

“0x300018000100000000000000006B0000564920205900000

00500E001002800426F62206A65727279″

Step-2: 

Now,we have deleted data but in Hex values but SQL keeps this data in a specific sequence so we can easily recover it.But before recovering the data we need to understand the format. This format is defined in detail in Kalen Delaney’s SQL Internal’s book.

• 1 Byte : Status Bit A
• 1 Byte : Status Bit B
• 2 Bytes : Fixed length size
• n Bytes : Fixed length data
• 2 Bytes : Total Number of Columns
• n Bytes : NULL Bitmap (1 bit for each column as 1 indicates that the column is null and 0 indicate that the column is not null)
• 2 Bytes : Number of variable-length columns
• n Bytes : Column offset array (2x variable length column)
• n Bytes : Data for variable length columns

So, the Hex data“RowLog content 0″ is equal to:

“Status Bit A + Status Bit B + Fixed length size + Fixed length data + Total Number of Columns + NULL Bitmap + Number of variable-length columns + NULL Bitmap+ Number of variable-length columns + Column offset array + Data for variable length columns.”

Step-3: 

Now, we need to break the RowLog Content o (Hex value of our deleted data) into the above defined structure.[Color codes are used for reference only]

• [Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)
• [Total No of Columns]= Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)
• [Null Bitmap length] = Ceiling ([Total No of Columns]/8.0)
• [Null Bytes]= Substring (RowLog content 0, Status Bit A+ Status Bit B +[Fixed Length Data] +1, [Null Bitmap length] )
• Total no of variable columns = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )
• Column Offset Array= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , Total no of variable columns*2 )
• Variable Column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+( Total no of variable columns*2)

Step-4: 

Now, we have the split of data as well,so we can find that which one column value is null or not by using Null Bytes. To achieve this convert Null Bytes (Hex value) into Binary format (As discussed, 1 indicates null for the column and 0 means there is some data).Here in this data, the Null Bitmap values are 00000111.We have only five column in student table (used as sample) and first five value of null bitmap is 00000.It means there is no null values.

Step-5:

Now, we have the primary data split (Step-3) and null values (Step-4) as well. After that we need to use this code snippet to get the column data like column name, column size, precision, scale and most importantly the leaf null bit (to ensure that the column is fixed data (<=-1) or variable data sizes (>=1)) of the table.

Select * from sys.allocation_units allocunits INNER JOINsys.partitions partitions ON (allocunits.type IN (1, 3) ANDpartitions.hobt_id = allocunits.container_id) OR (allocunits.type= 2 AND partitions.partition_id = allocunits.container_id) INNERJOIN sys.system_internals_partition_columns cols ONcols.partition_id = partitions.partition_id LEFT OUTER JOINsyscolumns ON syscolumns.id = partitions.object_id ANDsyscolumns.colid = cols.partition_column_id

And join it with our collected data table (Step-1,2,3,4) on the basis of allocunits.[Allocation_Unit_Id].Till now we know the information about the table and data,so we need to utilize this data to break [RowLog Contents 0] into table column data but in hex value. Here we need to take care as the data is either in fixed column size or in variable column size. .

Step-6: 

We collected data in hex value (Step-5) with respect to each column. Now we need to convert the data with respect to its data type defined as [System_type_id]. Each type is having different mechanism
for data conversion.

Step-7: 

Finally we do a pivot table over the data and you will see the result. THE DELETED DATA IS BACK.

Note: This data will only for display. It is not available in your selected table but you can insert this data in your table.

I’d really appreciate your comments on my posts, whether you agree or not, do comment.

Recover_Deleted_Data_Proc.sql

Problem

Someone has dropped a table from your database and you want to track who did it.  Or someone has deleted some data from a table, but no one will say who did.  In this tip, we will look at how you can use the transaction log to track down some of this information.

Solution

I have already discussed how to read the transaction log file in my last tip “How to read SQL Server Database Log file“. Before reading this tip, I recommend that you read the previous tip to understand how the transaction log file logs all database activity.

Here we will use the same undocumented function “fn_dblog” to find any unauthorized or unapproved deletes or table drops. This tip will help you track or find any unethical or an unwanted user who has dropped a table or deleted data from a table. I strongly suggest testing any undocumented functions in a lab environment first.

One way to find such users is with the help of the default trace, because the default trace captures and tracks database activity performed on your instance, but if you have a busy system the trace files may roll over far too fast and you may not be able to catch some of the changes in your database.  But these changes are also tracked in the transaction log file of the database and we will use this to find the users in question.

Finding a user who ran a DELETE statement

Step 1

Before moving ahead, we will create a database and a table on which I will delete some data. Run the below SQL code to create a database and table.

--Create DB.
USE [master];
GO
CREATE DATABASE ReadingDBLog;
GO
-- Create tables.
USE ReadingDBLog;
GO
CREATE TABLE [Location] (
    [Sr.No] INT IDENTITY,
    [Date] DATETIME DEFAULT GETDATE (),
    [City] CHAR (25) DEFAULT 'Bangalore');

Step 2

We have created a database named “ReadingDBLog” and a table ‘Location’ with three columns. Now we will insert a 100 rows into the table.

USE ReadingDBLog
GO
INSERT INTO Location DEFAULT VALUES ;
GO 100

Step 3

Now go ahead and delete some rows to check who has deleted your data.

USE ReadingDBLog
GO
DELETE Location WHERE [Sr.No]=10
GO
SELECT * FROM Location WHERE [Sr.No]=10
GO

You can see in the above screenshot that a row has been deleted from the table “Location”. I also ran a SELECT statement to verify the data has been deleted.

Step 4

Now we have to search the transaction log file to find the info about the deleted rows. Run the below command to get info about all deleted transactions.

USE ReadingDBLog
GO
SELECT 
    [Transaction ID],
    Operation,
    Context,
    AllocUnitName
    
FROM 
    fn_dblog(NULL, NULL) 
WHERE 
    Operation = 'LOP_DELETE_ROWS'

All transactions which have executed a DELETE statement will display by running the above command and we can see this in the above screenshot. As we are searching for deleted data in table Location, we can see this in the last row. We can find the table name in the “AllocUnitName” column. The last row says a DELETE statement has been performed on a HEAP table ‘dbo.Location’ under transaction ID 0000:000004ce. Now capture the transaction ID from here for our next command.

Step 5

We found the transaction ID from the above command which we will use in the below command to get the transaction SID of the user who has deleted the data.

USE ReadingDBLog
GO
SELECT
    Operation,
    [Transaction ID],
    [Begin Time],
    [Transaction Name],
    [Transaction SID]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] = '0000:000004ce'
AND
    [Operation] = 'LOP_BEGIN_XACT'

Here, we can see the [Begin Time] of this transaction which will also help filter out the possibilities in finding the exact info like when the data was deleted and then you can filter on the base of begin time when that command was executed.

We can read the above output as “A DELETE statement began at 2013/10/14 12:55:17:630 under transaction ID 0000:000004ce by user transaction SID 0x0105000000000005150000009F11BA296C79F97398D0CF19E8030000.

Now our next step is to convert the transaction SID hexadecimal value into text to find the real name of the user.

Step 6

Now we will figure out who ran the DELETE command. We will copy the hexadecimal value from the transaction SID column for the DELETE transaction and then pass that value into the SUSER_SNAME () function.

USE MASTER
GO   
SELECT SUSER_SNAME(0x0105000000000005150000009F11BA296C79F97398D0CF19E8030000)

Now we have found the user that did the delete.

Finding a user who ran a DROP statement

Step 1

Here I am going to drop table Location.

USE ReadingDBLog
GO
DROP TABLE Location

Step 2

Similarly if you drop any object or you perform anything operation in your database it will get logged in the transaction log file which will be visible by using this function fn_dblog.

Run the below script to display all logs which have been logged under DROPOBJ statement.

USE ReadingDBLog
GO
SELECT 
Operation,
[Transaction Id],
[Transaction SID],
[Transaction Name],
 [Begin Time],
   [SPID],
   Description
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
GO

Here we can find the transaction SID and all required info which we need to find the user.

Step 3

Now we can pass the transaction SID into system function SUSER_SNAME () to get the exact user name.

SELECT SUSER_SNAME(0x0105000000000005150000009F11BA296C79F97398D0CF19E8030000) 

Once again, we found the user in question.

Next Step

Use this function to do more research into your transaction log file. There is a lot of informative data in more than 100 columns when you use this command. You may also need to look into this and correlate with other data. Explore more knowledge on SQL Server Database Administration Tips.

Source

Let me explain it with simple example :

Create Table tbl_Sample 
([ID] int identity(1,1) ,
[Name] varchar(50))
GO
Insert into tbl_Sample values ('Letter A')
Insert into tbl_Sample values ('Letter B')
Insert into tbl_Sample values ('Letter C')

Select * from tbl_Sample

Now, you can change logins and delete records.

Given below is the code that can give you the recovered data with the user name who deleted it and the date and time as well.

-- Script Name: Recover_Deleted_Data_With_UID_Date_Time_Proc
-- Script Type : Recovery Procedure 
-- Develop By: Muhammad Imran
-- Date Created: 24 Oct 2012
-- Modify Date: 
-- Version    : 1.0
-- Notes      :

CREATE PROCEDURE Recover_Deleted_Data_With_UID_Date_Time_Proc
@Database_Name NVARCHAR(MAX),
@SchemaName_n_TableName NVARCHAR(Max),
@Date_From DATETIME='1900/01/01',
@Date_To DATETIME ='9999/12/31'
AS

DECLARE @RowLogContents VARBINARY(8000)
DECLARE @TransactionID NVARCHAR(Max)
DECLARE @AllocUnitID BIGINT
DECLARE @AllocUnitName NVARCHAR(Max)
DECLARE @SQL NVARCHAR(Max)
DECLARE @Compatibility_Level INT


SELECT @Compatibility_Level=dtb.compatibility_level
FROM
master.sys.databases AS dtb WHERE dtb.name=@Database_Name

Print @Compatibility_Level
--IF ISNULL(@Compatibility_Level,0)<=80
--BEGIN
--	RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1)
--	RETURN
--END

IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_SCHEMA]+'.'+[TABLE_NAME]=@SchemaName_n_TableName)=0
BEGIN
	RAISERROR('Could not found the table in the defined database',16,1)
	RETURN
END

DECLARE @bitTable TABLE
(
  [ID] INT,
  [Bitvalue] INT
)
--Create table to set the bit position of one byte.

INSERT INTO @bitTable
SELECT 0,2 UNION ALL
SELECT 1,2 UNION ALL
SELECT 2,4 UNION ALL
SELECT 3,8 UNION ALL
SELECT 4,16 UNION ALL
SELECT 5,32 UNION ALL
SELECT 6,64 UNION ALL
SELECT 7,128

--Create table to collect the row data.
DECLARE @DeletedRecords TABLE
(
    [Row ID]			INT IDENTITY(1,1),
    [RowLogContents]	VARBINARY(8000),
    [AllocUnitID]		BIGINT,
	[Transaction ID]	NVARCHAR(Max),
    [FixedLengthData]	SMALLINT,
	[TotalNoOfCols]		SMALLINT,
	[NullBitMapLength]	SMALLINT,
	[NullBytes]			VARBINARY(8000),
	[TotalNoofVarCols]	SMALLINT,
	[ColumnOffsetArray]	VARBINARY(8000),
	[VarColumnStart]	SMALLINT,
	[Slot ID]			INT,
    [NullBitMap]		VARCHAR(MAX)
    
)
--Create a common table expression to get all the row data plus how many bytes we have for each row.
;WITH RowData AS (
SELECT 

[RowLog Contents 0] AS [RowLogContents] 

,[AllocUnitID] AS [AllocUnitID] 

,[Transaction ID] AS [Transaction ID]  

--[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)
,CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) AS [FixedLengthData]  --@FixedLengthData

-- [TotalnoOfCols] =  Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)
,CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) as  [TotalNoOfCols]

--[NullBitMapLength]=ceiling([Total No of Columns] /8.0)
,CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) as [NullBitMapLength] 

--[Null Bytes] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [NullBitMapLength] )
,SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3,
CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))) as [NullBytes]

--[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )
,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END) AS [TotalNoofVarCols] 

--[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 )
,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN
SUBSTRING([RowLog Contents 0]
, CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) + 2
, (CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END)
* 2)  ELSE null  END) AS [ColumnOffsetArray] 

--	Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2)
,CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1)In (0x10,0x30,0x70)
THEN  (
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 4 

+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) 

+ ((CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END) * 2)) 

ELSE null End AS [VarColumnStart]
,[Slot ID]
FROM sys.fn_dblog(NULL, NULL)
WHERE
AllocUnitId IN
(SELECT [Allocation_unit_id] FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)  
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 
AND partitions.partition_id = allocunits.container_id)  
WHERE object_id=object_ID('' + @SchemaName_n_TableName + ''))

AND Context IN ('LCX_MARK_AS_GHOST', 'LCX_HEAP') AND Operation in ('LOP_DELETE_ROWS') 
And SUBSTRING([RowLog Contents 0], 1, 1)In (0x10,0x30,0x70)

/*Use this subquery to filter the date*/
AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL) 
WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')  
And [Transaction Name]='DELETE'
And  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)),

--Use this technique to repeate the row till the no of bytes of the row.
N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
           FROM N3 AS X, N3 AS Y)



INSERT INTO @DeletedRecords
SELECT	RowLogContents
		,[AllocUnitID]
		,[Transaction ID]
		,[FixedLengthData]
		,[TotalNoOfCols]
		,[NullBitMapLength]
		,[NullBytes]
		,[TotalNoofVarCols]
		,[ColumnOffsetArray]
		,[VarColumnStart]
        ,[Slot ID]
         ---Get the Null value against each column (1 means null zero means not null)
		,[NullBitMap]=(REPLACE(STUFF((SELECT ',' +
		(CASE WHEN [ID]=0 THEN CONVERT(NVARCHAR(1),(SUBSTRING(NullBytes, n, 1) % 2))  ELSE CONVERT(NVARCHAR(1),((SUBSTRING(NullBytes, n, 1) / [Bitvalue]) % 2)) END) --as [nullBitMap]
        
FROM
N4 AS Nums
Join RowData AS C ON n<=NullBitMapLength
Cross Join @bitTable WHERE C.[RowLogContents]=D.[RowLogContents] ORDER BY [RowLogContents],n ASC FOR XML PATH('')),1,1,''),',',''))
FROM RowData D

IF (SELECT COUNT(*) FROM @DeletedRecords)=0
BEGIN
	RAISERROR('There is no data in the log as per the search criteria',16,1)
	RETURN
END

DECLARE @ColumnNameAndData TABLE
(
 [Transaction ID]   varchar(100),
 [Row ID]			int,
 [Rowlogcontents]	varbinary(Max),
 [NAME]				sysname,
 [nullbit]			smallint,
 [leaf_offset]		smallint,
 [length]			smallint,
 [system_type_id]	tinyint,
 [bitpos]			tinyint,
 [xprec]			tinyint,
 [xscale]			tinyint,
 [is_null]			int,
 [Column value Size]int,
 [Column Length]	int,
 [hex_Value]		varbinary(max),
 [Slot ID]			int,
 [Update]			int
)

--Create common table expression and join it with the rowdata table
-- to get each column details
/*This part is for variable data columns*/
--@RowLogContents, 
--(col.columnOffValue - col.columnLength) + 1,
--col.columnLength
--)
INSERT INTO @ColumnNameAndData
SELECT 
[Transaction ID],
[Row ID],
Rowlogcontents,
NAME ,
cols.leaf_null_bit AS nullbit,
leaf_offset,
ISNULL(syscolumns.length, cols.max_length) AS [length],
cols.system_type_id,
cols.leaf_bit_position AS bitpos,
ISNULL(syscolumns.xprec, cols.precision) AS xprec,
ISNULL(syscolumns.xscale, cols.scale) AS xscale,
SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,
(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 
THEN
(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000
THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) - POWER(2, 15)
ELSE
CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))
END)
END)  AS [Column value Size],

(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0  THEN
(Case 

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And 
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000
THEN  (Case When [System_type_id]In (35,34,99) Then 16 else 24  end)

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And 
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000
THEN  (Case When [System_type_id]In (35,34,99) Then 16 else 24  end) --24 

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And 
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000
THEN (CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart]))

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And 
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN POWER(2, 15) +CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

END)

END) AS [Column Length]

,(CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN  NULL ELSE
 SUBSTRING
 (
 Rowlogcontents, 
 (

(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000
THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) - POWER(2, 15)
ELSE
CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))
END)

 - 
(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And 
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN  (Case When [System_type_id]In (35,34,99) Then 16 else 24  end) --24 
When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And 
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN  (Case When [System_type_id]In (35,34,99) Then 16 else 24  end) --24 
When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And 
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And 
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN POWER(2, 15) +CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

END)

) + 1,
(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And 
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN  (Case When [System_type_id] In (35,34,99) Then 16 else 24  end) --24 
When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And 
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN  (Case When [System_type_id] In (35,34,99) Then 16 else 24  end) --24 
When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And 
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN ABS(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart]))

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And 
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN POWER(2, 15) +CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

END)
)

END) AS hex_Value
,[Slot ID]
,0
FROM @DeletedRecords A
Inner Join sys.allocation_units allocunits On A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)
INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id
WHERE leaf_offset<0
UNION
/*This part is for fixed data columns*/
SELECT  
[Transaction ID],
[Row ID],
Rowlogcontents,
NAME ,
cols.leaf_null_bit AS nullbit,
leaf_offset,
ISNULL(syscolumns.length, cols.max_length) AS [length],
cols.system_type_id,
cols.leaf_bit_position AS bitpos,
ISNULL(syscolumns.xprec, cols.precision) AS xprec,
ISNULL(syscolumns.xscale, cols.scale) AS xscale,
SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,
(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 THEN max_length ELSE 0 END),0) FROM
sys.system_internals_partition_columns C WHERE cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5 AS [Column value Size],
syscolumns.length AS [Column Length]

,CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE
SUBSTRING
(
Rowlogcontents,(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 And C.leaf_bit_position=0 THEN max_length ELSE 0 END),0) FROM
sys.system_internals_partition_columns C where cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5
,syscolumns.length) END AS hex_Value
,[Slot ID]
,0
FROM @DeletedRecords A
Inner Join sys.allocation_units allocunits ON A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)
INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id
WHERE leaf_offset>0
Order By nullbit

Declare @BitColumnByte as int
Select @BitColumnByte=CONVERT(INT, ceiling( Count(*)/8.0)) from @ColumnNameAndData Where [System_Type_id]=104

;With N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
           FROM N3 AS X, N3 AS Y),
CTE As(
Select RowLogContents,[nullbit]
        ,[BitMap]=Convert(varbinary(1),Convert(int,Substring((REPLACE(STUFF((SELECT ',' +
		(CASE WHEN [ID]=0 THEN CONVERT(NVARCHAR(1),(SUBSTRING(hex_Value, n, 1) % 2))  ELSE CONVERT(NVARCHAR(1),((SUBSTRING(hex_Value, n, 1) / [Bitvalue]) % 2)) END) --as [nullBitMap]

from N4 AS Nums
Join @ColumnNameAndData AS C ON n<=@BitColumnByte And [System_Type_id]=104 And bitpos=0
Cross Join @bitTable WHERE C.[RowLogContents]=D.[RowLogContents] ORDER BY [RowLogContents],n ASC FOR XML PATH('')),1,1,''),',','')),bitpos+1,1)))
FROM @ColumnNameAndData D Where  [System_Type_id]=104)

Update A Set [hex_Value]=[BitMap]
from @ColumnNameAndData  A
Inner Join CTE B On A.[RowLogContents]=B.[RowLogContents]
And A.[nullbit]=B.[nullbit]


/**************Check for BLOB DATA TYPES******************************/
DECLARE @Fileid INT
DECLARE @Pageid INT
DECLARE @Slotid INT
DECLARE @CurrentLSN INT
DECLARE @LinkID INT
DECLARE @Context VARCHAR(50)
DECLARE @ConsolidatedPageID VARCHAR(MAX)
DECLARE @LCX_TEXT_MIX VARBINARY(MAX)

declare @temppagedata table 
(
[ParentObject] sysname,
[Object] sysname,
[Field] sysname,
[Value] sysname)

declare @pagedata table 
(
[Page ID] sysname,
[File IDS] int,
[Page IDS] int,
[AllocUnitId] bigint,
[ParentObject] sysname,
[Object] sysname,
[Field] sysname,
[Value] sysname)

DECLARE @ModifiedRawData TABLE
(
  [ID] INT IDENTITY(1,1),
  [PAGE ID] VARCHAR(MAX),
  [FILE IDS] INT,
  [PAGE IDS] INT,
  [Slot ID]  INT,
  [AllocUnitId] BIGINT,
  [RowLog Contents 0_var] VARCHAR(Max),
  [RowLog Length] VARCHAR(50),
  [RowLog Len] INT,
  [RowLog Contents 0] VARBINARY(Max),
  [Link ID] INT default (0),
  [Update] INT
)

            DECLARE Page_Data_Cursor CURSOR FOR 
            /*We need to filter LOP_MODIFY_ROW,LOP_MODIFY_COLUMNS from log for deleted records of BLOB data type& Get its Slot No, Page ID & AllocUnit ID*/
			SELECT LTRIM(RTRIM(Replace([Description],'Deallocated',''))) AS [PAGE ID]
			,[Slot ID],[AllocUnitId],NULL AS [RowLog Contents 0],NULL AS [RowLog Contents 0],Context
			FROM    sys.fn_dblog(NULL, NULL)  
			WHERE    
			AllocUnitId IN 
			(SELECT [Allocation_unit_id] FROM sys.allocation_units allocunits
			INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)  
			AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 
			AND partitions.partition_id = allocunits.container_id)  
			WHERE object_id=object_ID('' + @SchemaName_n_TableName + ''))
			AND Operation IN ('LOP_MODIFY_ROW') AND [Context] IN ('LCX_PFS') 
			AND Description Like '%Deallocated%' 
			/*Use this subquery to filter the date*/
			AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL) 
			WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')  
			AND [Transaction Name]='DELETE'
			AND  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
			GROUP BY [Description],[Slot ID],[AllocUnitId],Context

            UNION

			SELECT [PAGE ID],[Slot ID],[AllocUnitId]
            ,Substring([RowLog Contents 0],15,LEN([RowLog Contents 0])) AS [RowLog Contents 0]
            ,CONVERT(INT,Substring([RowLog Contents 0],7,2)),Context --,CAST(RIGHT([Current LSN],4) AS INT) AS [Current LSN]
			FROM    sys.fn_dblog(NULL, NULL)  
			WHERE   
			 AllocUnitId IN 
			(SELECT [Allocation_unit_id] FROM sys.allocation_units allocunits
			INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)  
			AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 
			AND partitions.partition_id = allocunits.container_id)  
			WHERE object_id=object_ID('' + @SchemaName_n_TableName + ''))
			AND Context IN ('LCX_TEXT_MIX') AND Operation in ('LOP_DELETE_ROWS') 
			/*Use this subquery to filter the date*/
			AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL) 
			WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')  
			And [Transaction Name]='DELETE'
			And  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
                        
			/****************************************/

		OPEN Page_Data_Cursor

		FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID,@LCX_TEXT_MIX,@LinkID,@Context

		WHILE @@FETCH_STATUS = 0
		BEGIN
			DECLARE @hex_pageid AS VARCHAR(Max)
			/*Page ID contains File Number and page number It looks like 0001:00000130.
			  In this example 0001 is file Number &  00000130 is Page Number & These numbers are in Hex format*/
			SET @Fileid=SUBSTRING(@ConsolidatedPageID,0,CHARINDEX(':',@ConsolidatedPageID)) -- Seperate File ID from Page ID
		
			SET @hex_pageid ='0x'+ SUBSTRING(@ConsolidatedPageID,CHARINDEX(':',@ConsolidatedPageID)+1,Len(@ConsolidatedPageID))  ---Seperate the page ID
 			SELECT @Pageid=Convert(INT,cast('' AS XML).value('xs:hexBinary(substring(sql:variable("@hex_pageid"),sql:column("t.pos")) )', 'varbinary(max)')) -- Convert Page ID from hex to integer
			FROM (SELECT CASE substring(@hex_pageid, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos) 
	        
            IF @Context='LCX_PFS' 	  
              BEGIN 
						DELETE @temppagedata
						INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 1) with tableresults,no_infomsgs;'); 
						INSERT INTO @pagedata SELECT @ConsolidatedPageID,@fileid,@pageid,@AllocUnitID,[ParentObject],[Object],[Field] ,[Value] FROM @temppagedata
              END
            ELSE IF @Context='LCX_TEXT_MIX' 
              BEGIN
                        INSERT INTO  @ModifiedRawData SELECT @ConsolidatedPageID,@fileid,@pageid,@Slotid,@AllocUnitID,NULL,0,CONVERT(INT,CONVERT(VARBINARY,REVERSE(SUBSTRING(@LCX_TEXT_MIX,11,2)))),@LCX_TEXT_MIX,@LinkID,0
              END	  
			FETCH NEXT FROM Page_Data_Cursor INTO  @ConsolidatedPageID, @Slotid,@AllocUnitID,@LCX_TEXT_MIX,@LinkID,@Context
		END
	
	CLOSE Page_Data_Cursor
	DEALLOCATE Page_Data_Cursor

	DECLARE @Newhexstring VARCHAR(MAX);

	--The data is in multiple rows in the page, so we need to convert it into one row as a single hex value.
	--This hex value is in string format
	INSERT INTO @ModifiedRawData ([PAGE ID],[FILE IDS],[PAGE IDS],[Slot ID],[AllocUnitId]
	,[RowLog Contents 0_var]
    , [RowLog Length])
	SELECT [Page ID],[FILE IDS],[PAGE IDS],Substring([ParentObject],CHARINDEX('Slot', [ParentObject])+4, (CHARINDEX('Offset', [ParentObject])-(CHARINDEX('Slot', [ParentObject])+4))-2 ) as [Slot ID]
	,[AllocUnitId]
	,Substring((
	SELECT 
    REPLACE(STUFF((SELECT REPLACE(SUBSTRING([Value],CHARINDEX(':',[Value])+1,CHARINDEX('†',[Value])-CHARINDEX(':',[Value])),'†','')
	FROM @pagedata C  WHERE B.[Page ID]= C.[Page ID] And Substring(B.[ParentObject],CHARINDEX('Slot', B.[ParentObject])+4, (CHARINDEX('Offset', B.[ParentObject])-(CHARINDEX('Slot', B.[ParentObject])+4)) )=Substring(C.[ParentObject],CHARINDEX('Slot', C.[ParentObject])+4, (CHARINDEX('Offset', C.[ParentObject])-(CHARINDEX('Slot', C.[ParentObject])+4)) ) And 
	[Object] Like '%Memory Dump%'  Order By '0x'+ LEFT([Value],CHARINDEX(':',[Value])-1)
	FOR XML PATH('') ),1,1,'') ,' ','')
	),1,20000) AS [Value]
    
    ,
     Substring((
	SELECT '0x' +REPLACE(STUFF((SELECT REPLACE(SUBSTRING([Value],CHARINDEX(':',[Value])+1,CHARINDEX('†',[Value])-CHARINDEX(':',[Value])),'†','')
	FROM @pagedata C  WHERE B.[Page ID]= C.[Page ID] And Substring(B.[ParentObject],CHARINDEX('Slot', B.[ParentObject])+4, (CHARINDEX('Offset', B.[ParentObject])-(CHARINDEX('Slot', B.[ParentObject])+4)) )=Substring(C.[ParentObject],CHARINDEX('Slot', C.[ParentObject])+4, (CHARINDEX('Offset', C.[ParentObject])-(CHARINDEX('Slot', C.[ParentObject])+4)) ) And 
	[Object] Like '%Memory Dump%'  Order By '0x'+ LEFT([Value],CHARINDEX(':',[Value])-1)
	FOR XML PATH('') ),1,1,'') ,' ','')
	),7,4) AS [Length]
    
	From @pagedata B
	Where [Object] Like '%Memory Dump%'
	Group By [Page ID],[FILE IDS],[PAGE IDS],[ParentObject],[AllocUnitId]--,[Current LSN]
	Order By [Slot ID]

	UPDATE @ModifiedRawData  SET [RowLog Len] = CONVERT(VARBINARY(8000),REVERSE(cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Length]"),0))', 'varbinary(Max)')))
	FROM @ModifiedRawData Where [LINK ID]=0

    UPDATE @ModifiedRawData  SET [RowLog Contents 0] =cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"),0))', 'varbinary(Max)')  
	FROM @ModifiedRawData Where [LINK ID]=0

	Update B Set B.[RowLog Contents 0] =
	(CASE WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NOT NULL THEN  A.[RowLog Contents 0]+C.[RowLog Contents 0] 
		WHEN A.[RowLog Contents 0] IS NULL AND C.[RowLog Contents 0] IS NOT NULL THEN  C.[RowLog Contents 0]
		WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NULL THEN  A.[RowLog Contents 0]  
		END)
    ,B.[Update]=ISNULL(B.[Update],0)+1
	from @ModifiedRawData B
	LEFT Join @ModifiedRawData A On A.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],15+14,2))))
	And A.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],19+14,2)))) 
    And A.[Link ID]=B.[Link ID]
    LEFT Join @ModifiedRawData C On C.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],27+14,2))))
	And C.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],31+14,2))))
	And C.[Link ID]=B.[Link ID]
    Where  (A.[RowLog Contents 0] IS NOT NULL OR C.[RowLog Contents 0] IS NOT NULL)


	Update B Set B.[RowLog Contents 0] =
	(CASE WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NOT NULL THEN  A.[RowLog Contents 0]+C.[RowLog Contents 0] 
		WHEN A.[RowLog Contents 0] IS NULL AND C.[RowLog Contents 0] IS NOT NULL THEN  C.[RowLog Contents 0]
		WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NULL THEN  A.[RowLog Contents 0]  
		END)
    --,B.[Update]=ISNULL(B.[Update],0)+1
	from @ModifiedRawData B
	LEFT Join @ModifiedRawData A On A.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],15+14,2))))
	And A.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],19+14,2)))) 
    And A.[Link ID]<>B.[Link ID] And B.[Update]=0
    LEFT Join @ModifiedRawData C On C.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],27+14,2))))
	And C.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],31+14,2))))
	And C.[Link ID]<>B.[Link ID] And B.[Update]=0
    Where  (A.[RowLog Contents 0] IS NOT NULL OR C.[RowLog Contents 0] IS NOT NULL)

	UPDATE @ModifiedRawData  SET [RowLog Contents 0] =  
    (Case When [RowLog Len]>=8000 Then 
    Substring([RowLog Contents 0] ,15,[RowLog Len]) 
    When [RowLog Len]<8000 Then 
    SUBSTRING([RowLog Contents 0],15+6,Convert(int,Convert(varbinary(max),REVERSE(Substring([RowLog Contents 0],15,6)))))
    End)
	FROM @ModifiedRawData Where [LINK ID]=0

	UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0] 
    --,A.[Update]=A.[Update]+1
	FROM @ColumnNameAndData A
	INNER JOIN @ModifiedRawData B ON 
	Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],17,4))))=[PAGE IDS]
	AND  Convert(int,Substring([hex_value],9,2)) =B.[Link ID] 
	Where [System_Type_Id] In (99,167,175,231,239,241,165,98) And [Link ID] <>0 

	UPDATE @ColumnNameAndData SET [hex_Value]=
    (CASE WHEN B.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NOT NULL THEN  B.[RowLog Contents 0]+C.[RowLog Contents 0] 
    WHEN B.[RowLog Contents 0] IS NULL AND C.[RowLog Contents 0] IS NOT NULL THEN  C.[RowLog Contents 0]
    WHEN B.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NULL THEN  B.[RowLog Contents 0]  
    END)
	--,A.[Update]=A.[Update]+1
	FROM @ColumnNameAndData A
	LEFT JOIN @ModifiedRawData B ON 
	Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],5,4))))=B.[PAGE IDS]  And B.[Link ID] =0 
   	LEFT JOIN @ModifiedRawData C ON 
	Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],17,4))))=C.[PAGE IDS]  And C.[Link ID] =0 
	Where [System_Type_Id] In (99,167,175,231,239,241,165,98)  And (B.[RowLog Contents 0] IS NOT NULL OR C.[RowLog Contents 0] IS NOT NULL)

	UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0] 
    --,A.[Update]=A.[Update]+1
	FROM @ColumnNameAndData A
	INNER JOIN @ModifiedRawData B ON 
	Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],9,4))))=[PAGE IDS]
    And Convert(int,Substring([hex_value],3,2))=[Link ID]
	Where [System_Type_Id] In (35,34,99) And [Link ID] <>0 
    
	UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0]
    --,A.[Update]=A.[Update]+10
	FROM @ColumnNameAndData A
	INNER JOIN @ModifiedRawData B ON 
	Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],9,4))))=[PAGE IDS]
	Where [System_Type_Id] In (35,34,99) And [Link ID] =0

	UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0] 
    --,A.[Update]=A.[Update]+1
	FROM @ColumnNameAndData A
	INNER JOIN @ModifiedRawData B ON 
	Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],15,4))))=[PAGE IDS]
	Where [System_Type_Id] In (35,34,99) And [Link ID] =0

    Update @ColumnNameAndData set [hex_value]= 0xFFFE + Substring([hex_value],9,LEN([hex_value]))
	--,[Update]=[Update]+1
    Where [system_type_id]=241

CREATE TABLE [#temp_Data]
(
    [FieldName]  VARCHAR(MAX),
    [FieldValue] NVARCHAR(MAX),
    [Rowlogcontents] VARBINARY(8000),
    [Row ID] int,
    [Transaction ID] VARCHAR(100),
    [Deletion Date Time] DATETIME,
    [Deleted By User Name] VARCHAR(Max)
)

INSERT INTO #temp_Data
SELECT NAME,
CASE
 WHEN system_type_id IN (231, 239) THEN  LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value)))  --NVARCHAR ,NCHAR
 WHEN system_type_id IN (167,175) THEN  LTRIM(RTRIM(CONVERT(VARCHAR(max),hex_Value)))  --VARCHAR,CHAR
 WHEN system_type_id IN (35) THEN  LTRIM(RTRIM(CONVERT(VARCHAR(max),hex_Value))) --Text
 WHEN system_type_id IN (99) THEN  LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value))) --nText 
 WHEN system_type_id = 48 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value)))) --TINY INTEGER
 WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value)))) --SMALL INTEGER
 WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) -- INTEGER
 WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))-- BIG INTEGER
 WHEN system_type_id = 61 Then CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (hex_Value))),100) --DATETIME
 WHEN system_type_id =58 Then CONVERT(VARCHAR(MAX),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(8000),REVERSE(hex_Value))),100) --SMALL DATETIME
 WHEN system_type_id = 108 THEN CONVERT(VARCHAR(MAX),CONVERT(NUMERIC(38,20), CONVERT(VARBINARY,CONVERT(VARBINARY(1),xprec)+CONVERT(VARBINARY(1),xscale))+CONVERT(VARBINARY(1),0) + hex_Value)) --- NUMERIC
 WHEN system_type_id =106 THEN CONVERT(VARCHAR(MAX), CONVERT(DECIMAL(38,20), CONVERT(VARBINARY,Convert(VARBINARY(1),xprec)+CONVERT(VARBINARY(1),xscale))+CONVERT(VARBINARY(1),0) + hex_Value)) --- DECIMAL
 WHEN system_type_id In(60,122) THEN CONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(8000),Reverse(hex_Value))),2) --MONEY,SMALLMONEY
 WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), hex_Value)%2))  -- BIT
 WHEN system_type_id =62 THEN  RTRIM(LTRIM(STR(CONVERT(FLOAT,SIGN(CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) * POWER(CAST(2 AS FLOAT),((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x7ff0000000000000) / EXP(52 * LOG(2))-1023))),53,LEN(hex_Value)))) --- FLOAT
 When system_type_id =59 THEN  Left(LTRIM(STR(CAST(SIGN(CAST(Convert(VARBINARY(8000),REVERSE(hex_Value)) AS BIGINT))* (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x007FFFFF) * POWER(CAST(2 AS Real), -23)) * POWER(CAST(2 AS Real),(((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS INT) )& 0x7f800000)/ EXP(23 * LOG(2))-127))AS REAL),23,23)),8) --Real
 WHEN system_type_id In (165,173) THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)') -- BINARY,VARBINARY
 WHEN system_type_id =34 THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)')  --IMAGE
 WHEN system_type_id =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value)) --UNIQUEIDENTIFIER
 WHEN system_type_id =231 THEN CONVERT(VARCHAR(MAX),CONVERT(sysname,hex_Value)) --SYSNAME
 WHEN system_type_id =241 THEN CONVERT(VARCHAR(MAX),CONVERT(xml,hex_Value)) --XML

 WHEN system_type_id =189 THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)') --TIMESTAMP
 WHEN system_type_id=98 THEN (CASE 
 WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(Substring(hex_Value,3,Len(hex_Value))))))  -- INTEGER
 WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=108 THEN CONVERT(VARCHAR(MAX),CONVERT(numeric(38,20),CONVERT(VARBINARY(1),Substring(hex_Value,3,1)) +CONVERT(VARBINARY(1),Substring(hex_Value,4,1))+CONVERT(VARBINARY(1),0) + Substring(hex_Value,5,Len(hex_Value)))) --- NUMERIC
 WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=167 THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),Substring(hex_Value,9,Len(hex_Value))))) --VARCHAR,CHAR
 WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,Substring((hex_Value),3,20))) --UNIQUEIDENTIFIER
 WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=61 THEN CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (Substring(hex_Value,3,LEN(hex_Value)) ))),100) --DATETIME
 WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=165 THEN '0x'+ SUBSTRING((CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)'),11,LEN(hex_Value)) -- BINARY,VARBINARY
 END)
 
END AS FieldValue
,[Rowlogcontents]
,[Row ID]
,[Transaction ID]
,null
,null
FROM @ColumnNameAndData ORDER BY nullbit

--Find the user ID and date time
Update #temp_Data Set [Deleted By User Name]=[name]
,[Deletion Date Time] = [Begin Time]
from #temp_Data  A
Inner Join fn_dblog(NULL,NULL) B On A.[Transaction ID]= B.[Transaction ID]
Inner Join sys.sysusers  C On B.[Transaction SID]=C.[Sid]
Where B.[Operation]='LOP_BEGIN_XACT' And B.[Context]='LCX_NULL' And B.[Transaction Name]='DELETE'

--Create the column name in the same order to do pivot table.

DECLARE @FieldName VARCHAR(max)
Declare @AdditionalField VARCHAR(max)
SET @FieldName = STUFF(
(
	SELECT ',' + CAST(QUOTENAME([Name]) AS VARCHAR(MAX)) FROM syscolumns WHERE id=object_id('' + @SchemaName_n_TableName + '')
	FOR XML PATH('')), 1, 1, '')

--Finally did pivot table and get the data back in the same format.

Set @AdditionalField=@FieldName + ' ,[Deleted By User Name],[Deletion Date Time]'

SET @sql = 'SELECT ' + @AdditionalField  + ' FROM #temp_Data PIVOT (Min([FieldValue]) FOR FieldName IN (' + @FieldName  + ')) AS pvt'
Print @sql
EXEC sp_executesql @sql

GO
--Execute the procedure like
--Recover_Deleted_Data_With_UID_Date_Time_Proc 'Database Name','Schema.table name'
--EXAMPLE #1 : FOR ALL DELETED RECORDS
EXEC Recover_Deleted_Data_With_UID_Date_Time_Proc 'test','dbo.tbl_sample' 
GO
--EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE
EXEC Recover_Deleted_Data_With_UID_Date_Time_Proc 'test','dbo.tbl_sample' ,'2011/12/01','2012/01/30'
--It will give you the result of all deleted records with the user name and date & time of deletion.

This little project started out as a basic script to connect to a Microsoft SqlServer and get data. It was a nightmare as I probably spent 15 hours learning about and troubleshooting both FreeTDS and UnixODBC. My pain is now your gain.

NOTICE: I have homebrew configured to install all packages into my local directory /Users/jared/.homebrew/

1) Install UnixODBC

[jared@localhost]$ brew install unixodbc
==> Downloading http://www.unixodbc.org/unixODBC-2.3.0.tar.gz
File already downloaded in /Users/jared/Library/Caches/Homebrew
==> ./configure --disable-debug --prefix=/Users/jared/.homebrew/Cellar/unixodbc/2.3.0 --enable-gui=no
==> make install
/Users/jared/.homebrew/Cellar/unixodbc/2.3.0: 24 files, 932K, built in 22 seconds
[jared@localhost]$

2) Edit the FreeTDS formula And install

What we are doing is changing the default tds version, enabling the msdblib, and pointing out where unixodbc installed.

require 'formula'
 
class Freetds > Formula
url 'http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-0.91.tar.gz'
homepage 'http://www.freetds.org/'
md5 'b14db5823980a32f0643d1a84d3ec3ad'
 
def install
system "./configure",·
"--prefix=#{prefix}",·
"--with-tdsver=7.0",·
"--enable-msdblib",
"--with-unixodbc=/Users/USERNAME/.homebrew/Cellar/unixodbc/2.3.0",
"--mandir=#{man}"
system 'make'
ENV.j1 # Or fails to install on multi-core machines
system 'make install'
end
end

[jared@localhost]$ brew install freetds

3) Start a new terminal session to make sure all your paths update

4) Confirm that you can connect to the server

We need to make sure that you can connect to the sqlserver and that the port is open and available to you.

To do this we use telnet. If you see the following, success! The port is open on the server.

[jared@localhost]$ telnet server.example.com 1433
Trying 192.168.1.101...
Connected to server.example.com.
Escape character is '^]'.

If you see the following. You failed. Check the Sqlserver configuration, firewalls, or network configuration.

[jared@localhost]$ telnet server.example.com 1433
Trying 192.168.1.101...
telnet: connect to address 192.168.1.101: Connection refused
telnet: Unable to connect to remote host

Note: Press the ctrl + ] keys to break to a prompt and then type exit.

5) Tsql

FreeTDS comes with a couple cli applications. One of them is tsql. It isn’t great, but I use it test and see if at least FreeTDS is working correctly. After you install FreeTDS using homebrew try and connect to the host using the following command.

[jared@localhost]$ tsql -H server.example.com -U USERNAME -P PASSWORD -v
 
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>exit

If you see a prompt, you haz awesome!

6) Sym link the FreeTDS and UnixODBC conf files

I create 3 sym links to the following files just for simplicity.

ln -s /Users/jared/.homebrew/Cellar/freetds/0.91/etc/freetds.conf ~/.freetds.conf
ln -s /Users/jared/.homebrew/Cellar/unixodbc/2.3.0/etc/odbc.ini ~/.odbc.ini
ln -s /Users/jared/.homebrew/Cellar/unixodbc/2.3.0/etc/odbcinst.ini ~/.odbcinst.ini

7) edit the .freetds.conf and add the following

[example]
host = server.example.com
port = 1433
tds version = 7.0

8 ) edit the odbcinst.ini and add the following

You are telling unixodbc where your FreeTDS drivers are located using this configuration file.

[FreeTDS]
Description = FreeTDS
Driver = /Users/jared/.homebrew/lib/libtdsodbc.so
Setup = /Users/jared/.homebrew/lib/libtdsodbc.so
UsageCount = 1

9) edit the .odbc.ini and add the following

[myexample]
Driver = FreeTDS // we just set this up a second ago
Description = MyExample
ServerName = example // this is the name of the configuration we used in the .freetds.conf file
UID = USERNAME
PWD = PASSWORD

10) isql should work

[jared@localhost]$ isql sqlinternal USERNAME PASSWORD
+---------------------------------------+
| Connected!
| sql-statement
| help [tablename]
| quit
+---------------------------------------+
SQL>quit

11) Osql Error

If you try osql, it throws an error.

[jared@localhost]$ osql -S myexample -U USERNAME -P PASSWORD
checking shared odbc libraries linked to isql for default directories...
/Users/jared/.homebrew/bin/osql: line 53: ldd: command not found
strings: can't open file: (No such file or directory)
osql: problem: no potential directory strings in "/Users/jared/.homebrew/bin/isql"
osql: advice: use "osql -I DIR" where DIR unixODBC\'s install prefix e.g. /usr/local
isql strings are:
checking odbc.ini files
reading /Users/jared/.odbc.ini
[myexample] found in /Users/jared/.odbc.ini
found this section:
[myexample]
Driver = FreeTDS
Description = MyExample
Servername = example
UID = USERNAME
PWD = PASSWORD
 
looking for driver for DSN [myexample] in /Users/jared/.odbc.ini
found driver line: " Driver = FreeTDS"
driver "FreeTDS" found for [myexample] in .odbc.ini
found driver named "FreeTDS"
"FreeTDS" is not an executable file
looking for entry named [FreeTDS] in /odbcinst.ini
found driver line: " Driver = /Users/jared/.homebrew/lib/libtdsodbc.so"
found driver /Users/jared/.homebrew/lib/libtdsodbc.so for [FreeTDS] in odbcinst.ini
/Users/jared/.homebrew/lib/libtdsodbc.so is not an executable file
osql: error: no driver found for sqlinternal
[jared@localhost]$

If you go through the error you will find that a certain driver is not executable. You just need to chmod the file.

[jared@localhost]$ chmod 554 /Users/jared/.homebrew/Cellar/freetds/0.91/lib/libtdsodbc.0.so

Now run it again.

[jared@localhost]$ osql -S myexample -U USERNAME -P PASSWORD
checking shared odbc libraries linked to isql for default directories...
/Users/jared/.homebrew/bin/osql: line 53: ldd: command not found
strings: can't open file: (No such file or directory)
osql: problem: no potential directory strings in "/Users/jared/.homebrew/bin/isql"
osql: advice: use "osql -I DIR" where DIR unixODBC\'s install prefix e.g. /usr/local
isql strings are:
checking odbc.ini files
reading /Users/jared/.odbc.ini
[myexample] found in /Users/jared/.odbc.ini
found this section:
[myexample]
Driver = FreeTDS
Description = myexamples
Servername = myexample
UID = USERNAME
PWD = PASSWORD
 
looking for driver for DSN [myexample] in /Users/jared/.odbc.ini
found driver line: " Driver = FreeTDS"
driver "FreeTDS" found for [myexample] in .odbc.ini
found driver named "FreeTDS"
"FreeTDS" is not an executable file
looking for entry named [FreeTDS] in /odbcinst.ini
found driver line: " Driver = /Users/jared/.homebrew/lib/libtdsodbc.so"
found driver /Users/jared/.homebrew/lib/libtdsodbc.so for [FreeTDS] in odbcinst.ini
/Users/jared/.homebrew/lib/libtdsodbc.so is an executable file
Using ODBC-Combined strategy
DSN [myexample] has servername "myexample" (from /Users/jared/.odbc.ini)
/Users/jared/.freetds.conf is a readable file
looking for [myexample] in /Users/jared/.freetds.conf
found this section:
[myexample]
host = myexample.bendcable.net
port = 1433
tds version = 7.0
 
Configuration looks OK. Connection details:
 
DSN: myexample
odbc.ini: /Users/jared/.odbc.ini
Driver: /Users/jared/.homebrew/lib/libtdsodbc.so
Server hostname: myexample.bendcable.net
Address: 192.168.12.103
 
Attempting connection as username ...
+ isql myexample USERNAME PASSWORD -v
+---------------------------------------+
| Connected!
| sql-statement
| help [tablename]
| quit
+---------------------------------------+
SQL> quit

SUCCESS!!!

Some other useful commands.

Useful commands

odbcinst -j
 
odbcinst -q -d
 
odbcinst -q -s

SQL Server provides an assortment of system views for accessing metadata about the server environment and its database objects. There are catalog views and information schema views and dynamic management views and several other types of views. DBAs and developers alike can benefit significantly from the rich assortment of information they can derive through these views, and it is worth the effort to get to know them.

System views are divided into categories that each serve a specific purpose. The most extensive category is the one that contains catalog views. Catalog views let you retrieve information about a wide range of system and database components—from table columns and data types to server-wide configurations.

Information schema views are similar to some of the catalog views in that they provide access to metadata that describes database objects such as tables, columns, domains, and check constraints. However, information schema views conform to the ANSI standard, whereas catalog views are specific to SQL Server.

In contrast to either of these types of views, dynamic management views return server state data that can be used to monitor and fine-tune a SQL Server instance and its databases. Like catalog views, dynamic management views are specific to SQL Server.

In this article, we’ll focus on these three types of views, looking at examples in each category. We won’t be covering the other types of system views because they tend not to be as commonly used, with perhaps a couple exceptions. For the most part, catalog, information schema, and dynamic management views are the ones you’ll likely be using the most often. But just so you know, the other types are related to replication and data-tier application (DAC) instances as well as provide compatibility with earlier SQL Server releases. Although they have their places, for now we’ll stick with the big three.

Catalog views

Of the various types of system views available in SQL Server, catalog views represent the largest collection and most diverse. You can use catalog views to gather information about such components as AlwaysOn Availability Groups, Change Data Capture, change tracking, database mirroring, full-text search, Resource Governor, security, Service Broker, and an assortment of other features—all in addition to being able to view information about the database objects themselves.

In fact, SQL Server provides so many catalog views that it would be nearly impossible—or at least highly impractical—to try look at all of them in one article, but know that there is a vast storehouse of views waiting for you, and they all work pretty much the same way.

Microsoft recommends that you use catalog views as your primary method for accessing SQL Server metadata because they provide the most efficient mechanism for retrieving this type of information. Through the catalog views you can access all user-available metadata. For example, the following SELECT statement returns information about databases whose name starts with adventureworks:

SELECT name, database_id, compatibility_level

FROM sys.databases

WHERE name LIKE 'adventureworks%';

The columns specified in the SELECT clause—name, database_id, and compatibility_level—represent only a fraction of the many columns supported by this view. The view will actually return nearly 75 columns worth of information about each database installed on the SQL Server instance. I’ve kept it short for the sake of brevity, as shown in the following results:

There is nothing remarkable here, except for the ease with which I was able to collect the metadata. The results include the database names, their auto-generated IDs, and their compatibility levels, which in both cases is 120. The120 refers to SQL Server 2014. (I created the examples in this article on a local instance of SQL Server 2014 running in a test virtual machine.)

The sys.databases view can also return information about database settings, such as whether the database is read-only or whether the auto-shrink feature is enabled. Many of the configuration-related columns take the bit data type to indicate whether a feature is on (1) or off (0).

As the preceding example illustrates, you access catalog views through the sys schema. Whichever view you use, it’s always a good idea to check the SQL Server documentation if you have any questions about its application to your particular circumstances. For example, the sys.databases view includes the state column, which provides status information such as whether a database is online, offline, or being restored. Each option is represented by one of nine predefined tinyint values. Some values in this column pertain only to certain environments. For instance, the value 7(copying) applies only to Azure SQL Database.

Now let’s look at the sys.objects catalog view, which returns a row for each user-defined, schema-scoped object in a database. The following SELECT statement retrieves the name and ID of all table-valued functions defined in the dboschema within the AdventureWorks2014 sample database:

USE AdventureWorks2014;
go

 

SELECT name, object_id

FROM sys.objects

WHERE SCHEMA_NAME(schema_id) = 'dbo'

AND type_desc = 'sql_table_valued_function';

 

Notice that I use the SCHEMA_NAME built-in function to match the schema ID to dbo in the WHERE clause. Functions such as SCHEMA_NAME, OBJECT_ID, OBJECT_NAME, and so on can be extremely useful when working with catalog views.

Also in the WHERE clause, I match the type_desc column to sql_table_valued_function, giving me the following results:

The sys.objects view is a handy tool to have because it provides quick and easy access to all user-defined objects in your database, including tables, views, triggers, functions, and constraints. However, SQL Server also provides catalog views that are distinct to a specific object type. For example, the following SELECT statement retrieves data through the sys.tables view:

USE AdventureWorks2014;
go

SELECT name, max_column_id_used

FROM sys.tables

WHERE SCHEMA_NAME(schema_id) = ‘HumanResources’

The statement returns a list of all tables in the HumanResources schema, along with the maximum column ID used for each table, as shown in the following results:

The interesting thing about the sys.tables view is that it inherits all the columns from the sys.objects view and then adds additional columns with table-specific information. For example, in the preceding example, the namecolumn is inherited from sys.objects but the max_column_id_used column is specific to sys.tables. (For information about which views inherit columns from other views, refer to the SQL Server documentation.)

You can also join catalog views to retrieve specific types of information. For example, the following SELECTstatement joins the sys.columns view to the sys.types view to retrieve information about the Person table:

USE AdventureWorks2014;

go

 

SELECT c.name AS ColumnName,

t.name AS DataType,

CASE t.is_user_defined

WHEN 1 THEN 'user-defined type'

ELSE 'system type' END AS UserOrSystem

FROM sys.columns c JOIN sys.types t

ON c.user_type_id = t.user_type_id

WHERE c.object_id = OBJECT_ID('Person.Person');

Not surprisingly, the sys.columns view returns a list of columns in the table, and the sys.types view returns the name of the column data types, along with whether they are system types or user-defined:

Up to this point, the catalog views we’ve looked at have focused on the databases and their objects. However, we can use catalog views to retrieve all sorts of information, such as details about database files:

USE AdventureWorks2014;

go

SELECT file_id, name, state_desc, type_desc

FROM sys.database_files

WHERE name LIKE ‘adventureworks%’;

In this case, we’re using the sys.database_files view to retrieve the file ID, file name, file state, and file type.

We might instead use the sys.assembly_types view to return information about any assemblies added to the database:

USE AdventureWorks2014;

go

SELECT name, user_type_id, assembly_class

FROM sys.assembly_types;

As the following results show, the AdventureWorks2014 database includes three assemblies, all of which are SQL Server’s advanced data types:

You can even retrieve data about security-related metadata within your database. For example, the following SELECTstatement uses the sys.database_principals view to return the names and IDs of all security principals in theAdcentureWorks2014 database:

USE AdventureWorks2014;

go

SELECT name, principal_id

FROM sys.database_principals

WHERE type_desc = ‘DATABASE_ROLE’;

Notice that we’ve used a WHERE clause to qualify our query so the SELECT statement returns only theDATABASE_ROLE principal type:

Of course, SQL Server security occurs at the database level and at the server level. To address the server level, SQL Server also includes catalog views specific to the current instance. For example, the following SELECT statement joins the sys.server_principals view and the sys.server_permissions view to retrieve information about the server principals and their permissions:

SELECT pr.name, pr. principal_id,

pm.permission_name, pm.state_desc

FROM sys.server_principals pr

JOIN sys.server_permissions AS pm

ON pr.principal_id = pm.grantee_principal_id

WHERE pr.type_desc = ‘SERVER_ROLE’;

In this case, we’re concerned only with the SERVER_ROLE principal type, so we’ve added the WHERE clause, giving us the following results:

You can also use catalog views to retrieve server configuration information. For instance, the following SELECTstatement uses the the sys.configurations view to retrieve configuration information about the current server:

SELECT name, description

FROM sys.configurations

WHERE is_advanced = 1 AND is_dynamic = 0;

In this case, we’ve limited our query to non-dynamic advanced settings, as shown in the following results:

There are, of course, many more examples of catalog views I can show you, but you get the point. There’s a great deal of information to be had, and I’ve barely scratched the surface. For a complete listing of the available catalog views, check out the MSDN topic Catalog Views (Transact-SQL).

Information schema views

Information schema views provide a standardized method for querying metadata about objects within a database. The views are part of the schema INFORMATION_SCHEMA, rather than the sys schema, and are much more limited in scope than catalog views. At last count, SQL Server was providing only 21 information schema views, compared to over 200 catalog views.

The advantage of using information schema views is that, because they are ANSI-compliant, you can theoretically migrate your code to different database systems without having to update your view references. If portability is important to your solution, you should consider information schema views, just know that they don’t do nearly as much as catalog views. And, of course, using one type of view doesn’t preclude you from using another type of view.

With information schema views, you can retrieve metadata about database objects such as tables, constraints, columns, privileges, views, and domains. (In the world of information schema views, a domain is a user-defined data type, and a catalog is the database itself.)

Let’s look at a few examples. The first one uses the TABLES view to retrieve the name and type of all the tables and views in the Purchasing schema:

USE AdventureWorks2014;

go

SELECT TABLE_NAME, TABLE_TYPE

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = ‘purchasing’

ORDER BY TABLE_NAME;

No magic here. Just a simple query that returns basic information, as shown in the following results:

We could have also retrieved the TABLE_CATALOG and TABLE_SCHEMA columns, which are included in the view to provide fully qualified, four-part names for each object, but we didn’t need that information in this case, and the table includes no other columns, falling far short of what you get with sys.tables.

Now let’s pull data through the COLUMNS view, which provides a few more details than we get with TABLES:

USE AdventureWorks2014;

go

SELECT COLUMN_NAME, DATA_TYPE, DOMAIN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = ‘person’

AND TABLE_NAME = ‘contacttype’;

In this case, our query retrieves the column name, system data type, and user data type, if any, within the Person.ContactType table. In this case, the table includes only one user-defined data type (Name):

Now suppose we want to retrieve a list of user-defined data types in the AdventureWorks2014 database, along with the base type for each one:

USE AdventureWorks2014;

go

SELECT DOMAIN_NAME, DATA_TYPE

FROM INFORMATION_SCHEMA.DOMAINS

ORDER BY DOMAIN_NAME;

This time, we use the DOMAINS view, which gives us the following results (at least on my system):

Let’s look at one more example, this one of the CHECK_CONSTRAINTS view, which retrieves information about the check constraints in the Person schema:

USE AdventureWorks2014;

go

SELECT CONSTRAINT_NAME, CHECK_CLAUSE

FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS

WHERE CONSTRAINT_SCHEMA = ‘person’;

In this case, we get the name of the check constraints, along with the constraint definitions:

That’s all there is to information schema views. There are relatively few of them and the ones that are there contain relatively little information, when compared to their catalog counterparts. You can find more details about information schema views by referring to the MSDN topic Information Schema Views (Transact-SQL).

Dynamic management views

With dynamic management views, we move into new territory. The views return server state information about your databases and servers, which can be useful for monitoring your systems, tuning performance, and diagnosing any issues that might arise.

Like catalog views, dynamic management views provide a wide range of information. For example, SQL Server includes a set of dynamic management that are specific to memory-optimized tables. One of these, dm_xtp_system_memory_consumers, returns information about database-level memory consumers:

SELECT memory_consumer_desc, allocated_bytes, used_bytes

FROM sys.dm_xtp_system_memory_consumers

WHERE memory_consumer_type_desc = ‘pgpool’;

The statement retrieves the consumer description, the amount of allocated bytes, and the amount of used bytes for the pgpool consumer type, giving us the following results.

Like catalog views, dynamic management views are part of the sys schema. In addition, their names always begin with the dm_ prefix. Unfortunately, Microsoft uses the same naming convention for SQL Server’s dynamic management functions. But you’ll quickly discover which ones are which when you try to run them and you’re prompted to provide input parameters. (I’ll save a discussion about the functions for a different article.)

Another category of dynamic management views focuses on the SQL Server Operating System (SQLOS), which manages the operating system resources specific to SQL Server. For example, you can use the dm_os_threadsview to retrieve a list of SQLOS threads running under the current SQL Server process:

SELECT os_thread_id, kernel_time, usermode_time

FROM sys.dm_os_threads

WHERE usermode_time > 300;

The statement returns the thread ID, kernel time, and user time, for those threads greater than 300 milliseconds, giving us the following results (on my test system):

The SQLOS views even include one that returns miscellaneous information about the computer and its resources:

SELECT cpu_count, physical_memory_kb, virtual_memory_kb

FROM sys.dm_os_sys_info;

Although the dm_os_sys_info view can return a variety of information about the environment, in this case, we’ve limited that information to the CPU count, physical memory, and virtual memory:

SQL Server also includes dynamic management views for retrieving information about the indexes. For example, you can use the db_index_usage_stats view to return details about different types of index operations:

SELECT index_id, user_seeks, user_scans

FROM sys.dm_db_index_usage_stats

WHERE object_id = OBJECT_ID(‘AdventureWorks2014.HumanResources.Employee’);

The statement returns the data shown in the following table:

Being able to query statistics about an index in this way can be useful when testing an application’s individual operations. This can help you pinpoint whether your queries are using the indexes effectively or whether you might need to build different indexes. Note, however, that index statistics can reflect all activity, whether generated by an application or generated internally by SQL Server.

Dynamic management views are either server-scoped or database-scoped. The ones we’ve look at so far have been server-scoped, even the dm_db_index_usage_stats index shown in the last example. In that case, however, we were concerned with only the AdventureWorks2014 database, so we specified the database in our WHERE clause.

If you want to run a database-scoped dynamic management view, you must do so within the context of the target database. In the following SELECT statement, I use the dm_db_file_space_usage view to return space usage data about the data file used by the AdventureWorks2014 database:

USE AdventureWorks2014;

go

SELECT total_page_count, allocated_extent_page_count, unallocated_extent_page_count

FROM sys.dm_db_file_space_usage

WHERE file_id = 1;

All I’m doing here is retrieving the total page count, allocated extent page count, and unallocated extent page count:

Note that page counts are always at the extent level, which means they will be multiples of eight.

We can instead use the dm_db_fts_index_physical_stats view to retrieve data about the full-text and sematic indexes in each table:

USE AdventureWorks2014;

go

SELECT OBJECT_NAME(object_id) ObjectName,

object_id ObjectID, fulltext_index_page_count IndexPages

FROM sys.dm_db_fts_index_physical_stats;

This time we get the object name and ID of the table that contains the index, as well as the page count for each index:

Let’s look at one more dynamic management view that is database-scoped. Thedm_db_persisted_sku_features view returns a list of edition-specific features that are enabled in the current database, but are not supported on all SQL Server versions. The view applies to SQL Server 2008 through the current version. The following SELECT statement uses the view to retrieve the feature name and ID:

USE AdventureWorks2014;

go

SELECT feature_name, feature_id

FROM sys.dm_db_persisted_sku_features;

In this case, the SELECT statement returns only one row:

The dm_db_persisted_sku_features view includes the feature_id column only for informational purposes. The column is not supported and may not be part of the view in the future.

Although these are but a few of the dynamic management views that SQL Server supports, the examples should give you a good sense of the variety of data they can provide. For a complete list of dynamic management views and to learn more about each one, refer to the MSDN topic Dynamic Management Views and Functions (Transact-SQL).

Plenty more where that came from

As mentioned earlier, SQL Server also provides system views to support backward compatibility, replication, and DAC instances. The compatibility views might come in handy if you’re still running SQL Server 2000. You might also find the replication-related views useful if you’ve implemented replication, although Microsoft recommends that you instead use the stored procedures available for accessing replication metadata. As for the DAC views, SQL Server provides only two of them, and they reside only in the msdb database.

For many DBAs and database developers, the catalog views and dynamic management views will likely be their first line of defense when retrieving SQL Server metadata, whether it’s specific to particular database objects or the server environment as a whole. That’s not to diminish the importance of the other views, but rather to point out that Microsoft has put most of its effort into building an extensive set of catalog views and dynamic management views. And given all the work that’s gone into them, there’s certainly no reason not to take advantage of what’s available.

https://www.simple-talk.com/sql/learn-sql-server/sql-server-system-views-the-basics/