BLOB Index Columns

Tags: , , ,
Comments: No Comments
Published on: April 27, 2010

For the April T-SQL Tuesday, I blogged about a report to find information on the BLOBs in a database.  I have since seen a request to add to that script.  The addition would add some good information concerning the columns involved in a BLOB index.  This information is to find all of the columns that are involved in the index that includes a BLOB in the index.

Base Script

In that article I posted a script to help arrive at the final report.  There were a couple of things required for the setup.  I am including all of that information here in a single script.

[codesyntax lang=”tsql”]


In this script, I made a slight alteration from the article I posted.  In that article, I somehow missed a change to the script I had been testing.  That change is in the Temp table that I created (to properly support the Join statements on each side of the Union Select statement).  I simply added the object_id.

The Change

[codesyntax lang=”tsql”]


The guts of the change to add in the columns for this script comes with the following segment of code.

[codesyntax lang=”tsql”]


With that snippet, I also needed to Join it to the select statements, and thus it was added on both sides of the Union statement as illustrated in the next example.

[codesyntax lang=”tsql”]


In the above CTE, you will note that I used the STUFF function along with a FOR XML Path statement.  The combination of these statements allows one to concatenate a list into a comma separated list as I have done with the ColList (column list) column.  I also want to note here that I am using the text() keyword along with the FOR XML Path.  There are other methods of returning information back to the screen when using FOR XML Path.  I chose to use the text() in this case because I am just returning a concatenated list of columns that really only should read as text.  If I were returning a SQL statement, I would choose a different method to make the text more readable.


I chose to make this subtle change via the CTE due to the ease of understanding and readability of the code for me.  By illustrating the columns involved in an index that is on a BLOB column, one can gain greater insight into the use of the database.  I am glad that this change was requested because it makes sense to me.

I hope you find it useful.

Edit: Made a correction to the first script

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

April 2010
« Mar   May »

Welcome , today is Monday, January 27, 2020