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

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)
-----------------------------------------------------------------------------------------------------------------------

"sjoshi" <(E-Mail Removed)> wrote in message
news:4e5c2540-f9f4-4cd6-a9e6-(E-Mail Removed)...
>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