List all tables without LOB columns
-
Comments posted to this topic are about the item List all tables without LOB columns
-
Could you expand on the benefit of this in relation to online indexing?
Thanks,
Mark
-
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.
-
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)
)
-
Without the ('char','nchar','varchar','nvarchar') though
Those arent LOB columns
-
Thank you very much.
-Mark
-
You are right.
I was following the original poster's code in functionality, looking for char fields of maximum size.
-
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')
-
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
-
Thanks for the script.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply