Technical Article

List all tables without LOB columns

,

Lists all the tables in a database that do not contain any LOB columns.

Useful when rebuilding indexes online.

SELECT DISTINCT '['+ic.TABLE_SCHEMA+'].'+'['+ic.TABLE_NAME+']' 
FROM INFORMATION_SCHEMA.COLUMNS ic
Inner Join sys.sysobjects so
ON ic.TABLE_NAME = so.name
Inner Join sys.sysindexes si
ON so.id = si.id
where SO.type = 'U' 
and TABLE_SCHEMA+TABLE_NAME not in 
(SELECT DISTINCT ic.TABLE_SCHEMA+ic.Table_Name 
FROM INFORMATION_SCHEMA.COLUMNS ic
Inner Join sys.sysobjects so
ON ic.Table_Name = so.name
Inner Join sys.sysindexes si
ON so.id = si.id
WHERE
ic.DATA_TYPE IN('text', 'ntext', 'image')
OR CHARACTER_MAXIMUM_LENGTH = '-1')

Rate

4.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

4.5 (2)

You rated this post out of 5. Change rating