August 29, 2011 at 8:17 pm
Comments posted to this topic are about the item List all tables without LOB columns
August 30, 2011 at 6:46 am
Could you expand on the benefit of this in relation to online indexing?
Thanks,
Mark
August 30, 2011 at 6:52 am
A Clustered Index whose base table that has any column that is LOB cannot be rebuilt online.
A Non clustered index whose base table has any non key LOB columns participating in the index cannot be rebuilt online.
August 30, 2011 at 7:14 am
You could do this query without resorting to using sysobjects and sysindexes (use information_schema)
For example:
select table_name
from information_schema.tables
where table_name not in (select distinct table_name
from information_schema.columns
where data_type in('text','ntext','image')
or (data_type in('char','nchar','varchar','nvarchar')
and character_maximum_length=-1)
)
August 30, 2011 at 7:22 am
Without the ('char','nchar','varchar','nvarchar') though
Those arent LOB columns
August 30, 2011 at 7:26 am
Thank you very much.
-Mark
August 30, 2011 at 7:36 am
You are right.
I was following the original poster's code in functionality, looking for char fields of maximum size.
October 10, 2011 at 1:35 am
More simplified form:-)
SELECT '['+SCHEMA_NAME(SCHEMA_ID)+'].['+name+']' TableName
FROM Sys.tables WHERE name NOT IN (
SELECT Distinct ic.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS ic
INNER JOIN Sys.tables st ON ic.TABLE_NAME=st.name
WHERE ic.DATA_TYPE IN('text', 'ntext', 'image')
OR CHARACTER_MAXIMUM_LENGTH = '-1')
October 10, 2011 at 1:38 am
Most simplified form 🙂
SELECT '['+SCHEMA_NAME(SCHEMA_ID)+'].['+name+']' TableName
FROM Sys.tables WHERE name NOT IN (
SELECT Distinct ic.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS ic
WHERE ic.DATA_TYPE IN('text', 'ntext', 'image')
OR CHARACTER_MAXIMUM_LENGTH = '-1')
Aqeel Ahmed
May 12, 2016 at 7:22 am
Thanks for the script.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply