Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments   |   Related Tips: More > Database Design

Attend these FREE SQL Server 2017 webcasts >> click to register


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
BLOG 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 Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools