Check if table has LOB columns

[sjoshi]

I need to check if a table in SQLServer 2005 is made up of one of the
LOB columns :image, ntext, text, varchar(max), nvarchar(max),
varbinary(max), and xml

Currently I'm using SMO with code like this...

private bool TableHasLOBColumns(ColumnCollection tableColns)
{
bool result = false;
foreach (Column cl in tableColns)
{
if (checkSQLTypes.Exists(delegate(SqlDataType match)
{
return match.Equals(cl.DataType.SqlDataType);
}))
{
result = true;
break;
}
} return result;
}

Where checkSQLTypes is defined as

private static List<SqlDataType> checkSQLTypes = new
List<SqlDataType>();

checkSQLTypes.AddRange(new SqlDataType[] { SqlDataType.Image,
SqlDataType.NText, SqlDataType.Text,
SqlDataType.NVarCharMax,
SqlDataType.VarCharMax, SqlDataType.VarBinaryMax, SqlDataType.Xml });

However I find the routine using SMO to be too slow especially when
one has 500+ tables to check. Is there a quicker/simpler way to do
this ??

thanks
Sunit

 

[William Vaughn]

I would experiment with the ADO.NET 2.0 GetSchema Connection class. It might
work better.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)