Find tables with Large Obejct (LOB) data types in a database

From SQL Server 2005 onwards, the indexes can be re-indexed ONLINE. This ensures that the underlying tables and indexes are available for other users when the re-indexing is taking place. Also Standard and Workgroup editions of SQL

The limitation for ONLINE Re-indexing is that it cannot be run on tables which have Large Object Data type columns (image, text, ntext, varchar(max), nvarchar(max), varbinary(max), xml). The Alter Index documentation on MSDN has more details on this.

Yesterday, I responded to a question which read “How to find out LOB data types in a table?”. There are many ways to find it out, here is the one which I use frequently.

SELECT a.name as [ColumnName],B.name as [Table Name]
	FROM SYS.COLUMNS A
	JOIN SYS.OBJECTS B ON A.object_id = B.object_id
	WHERE A.system_type_id in (35,34,241,99)
	AND B.type ='U'
	AND B.name <>'dtproperties'
UNION
	SELECT a.name, B.NAME
	FROM SYS.COLUMNS A
	JOIN SYS.OBJECTS B ON A.object_id = B.object_id
	WHERE A.system_type_id IN (167,231,165)
	AND A.max_length = -1
	AND B.type ='U'
	AND B.name <>'dtproperties'

With this script, the LOB data type columns can be identified and is useful while trying to reindex selected tables online.

2 thoughts on “Find tables with Large Obejct (LOB) data types in a database

  1. Pingback: Rebuild Indexes | Wisdom Soup

  2. Pingback: Generate script for objects along with data using Microsoft SQL Server Database Publishing Wizard | SQLServerPedia

Comments are closed.