Identify All SQL Server Tables with Columns of a BLOB Data Type


By:   |   Updated: 2007-06-15   |   Comments   |   Related: More > Database Design


Problem

After some recent SQL Server performance tuning, I noticed that one of my large core tables (important) has a column with a text data type.  I had no idea that this table had a BLOB (binary large object) data type.  After running some quick queries on the table\column and doing some quick analysis I have found that none of the entries exceed a few hundred characters.  After talking to the development team, they indicated that a 500 character limit is enforced on the front end.

With this being said, I clearly do not need a column with the text data type and could use a varchar or nvarchar (Unicode) column with a width of 500.  Since I was not aware of this particular BLOB column in one of my large core tables (important), I suspect some other tables in my environment have column text, image, ntext, etc data types.  How can I easily find these columns on a per table basis without having to review them in SQL Server Enterprise Manager or SQL Server Management Studio?

Solution

BLOB data types (text, image, ntext, etc) may be a performance and storage issue in your environment especially if they are not needed.  If you notice that queries that have the BLOG data types are performing poorly, it is worth trying to understand how the data is used and some alternatives to address the issue.  As such, review the following scripts that query the corresponding system objects in SQL Server 2005 and SQL Server 2000 to determine if columns are using a BLOB data type:

SQL Server 2005

SELECT o.[name], o.[object_id ], c.[object_id ], c.[name], t.[name]
FROM sys.all_columns c
INNER JOIN sys.all_objects o
ON c.object_id = o.object_id
INNER JOIN sys.types t
ON c.system_type_id = t.system_type_id
WHERE c.system_type_id IN (35, 165, 99, 34, 173)
AND o.[name] NOT LIKE 'sys%'
AND o.[name] <> 'dtproperties'
AND o.[type] = 'U'
GO

SQL Server 2000

SELECT o.[name], o.[id], c.[id], c.[name], t.[name]
FROM dbo.syscolumns c
INNER JOIN dbo.sysobjects o
ON c.id = o.id
INNER JOIN dbo.systypes t
ON c.xtype = t.xtype
WHERE c.xtype IN (35, 165, 99, 34, 173)
AND o.[name] NOT LIKE 'sys%'
AND o.[name] <> 'dtproperties'
AND o.xtype = 'U'
GO

SQL Server System Data Type Lookup Values

As a point of reference, below outlines the SQL Server system data type lookup values:
  • 35 - text
  • 165 - varbinary
  • 99 - ntext
  • 34 - image
  • 173 - binary
Next Steps
  • Check out some of the databases in your development and test environments to see if they are using text, image, ntext, etc data types unexpectedly.  If so, consider researching the issue further to see if the BLOB data type is really needed.
  • Conduct some analysis on the tables\columns to find out an appropriate data type and length.
  • Contact your development team to find out what data length is supported in the front end and middle tier of the application.  In addition, validate with the development team that they are open to changing the data type and length as well as pick their brains in terms of any potential issues prior to testing.
  • Make the application and database changes then test your applications to ensure they functionally operate as expected.  In addition, take a peek at the performance for some of the queries to see their improvement.
  • If you are unsure about the benefits of changing unneeded BLOB data types to varchar or nvarchar data types consider the following items as you test your applications and note these impacts in your environment:
    • Performance improvements
    • Storage reduction
    • Improved backup and recovery times
  • Check out this related tip - How to get length of data in Text, NText and Image columns


Last Updated: 2007-06-15


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at MSSQLTips.com, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips





Comments For This Article





download





Recommended Reading

Find and Remove Duplicate Rows from a SQL Server Table

Working with SQL Server Extended Properties

Surrogate Key vs Natural Key Differences and When to Use in SQL Server

What is a GUID in SQL Server

SQL Server Database Diagram Tool in Management Studio








get free sql tips
agree to terms


Learn more about SQL Server tools