Prev: 80 Compatibility Mode
Next: junction table query
From: Mikael Sorensen on 29 Mar 2010 06:27
Hi,

How do I query a database to identify indexes that contain fields with a LOB
data type in a SQL Server database?

Br.
Mikael



From: jgurgul on 29 Mar 2010 11:29
Hi,

You should be able to selectively filter the types you are interested in.

SELECT
OBJECT_NAME(ic.[object_id]) AS ObjectName,
i.[name] AS IndexName,
sc.[name] AS ColumnName,
st.[name],
sc.max_length
FROM
sys.index_columns AS ic INNER JOIN
sys.indexes AS i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN
sys.columns AS sc ON ic.column_id = sc.column_id AND ic.object_id =
sc.object_id INNER JOIN
sys.types AS st ON sc.user_type_id = st.user_type_id
WHERE OBJECT_NAME(ic.[object_id]) <> 'sysdiagrams'
AND OBJECTPROPERTY( i.object_id, 'IsMSShipped' ) = 0
ORDER BY 2,3

Jon

"Mikael Sorensen" wrote:

> Hi,
>
> How do I query a database to identify indexes that contain fields with a LOB
> data type in a SQL Server database?
>
> Br.
> Mikael
>
>
>
> .
>
From: Mikael Sorensen on 29 Mar 2010 13:48
Thanks. Solved by probem.

Br.
Mikael

"jgurgul" <jgurgul(a)discussions.microsoft.com> wrote in message
news:23FDBE56-51CD-431A-9CD1-4CB821B22284(a)microsoft.com...
> Hi,
>
> You should be able to selectively filter the types you are interested in.
>
> SELECT
> OBJECT_NAME(ic.[object_id]) AS ObjectName,
> i.[name] AS IndexName,
> sc.[name] AS ColumnName,
> st.[name],
> sc.max_length
> FROM
> sys.index_columns AS ic INNER JOIN
> sys.indexes AS i ON ic.object_id = i.object_id AND ic.index_id =
> i.index_id
> INNER JOIN
> sys.columns AS sc ON ic.column_id = sc.column_id AND ic.object_id =
> sc.object_id INNER JOIN
> sys.types AS st ON sc.user_type_id = st.user_type_id
> WHERE OBJECT_NAME(ic.[object_id]) <> 'sysdiagrams'
> AND OBJECTPROPERTY( i.object_id, 'IsMSShipped' ) = 0
> ORDER BY 2,3
>
> Jon
>
> "Mikael Sorensen" wrote:
>
>> Hi,
>>
>> How do I query a database to identify indexes that contain fields with a
>> LOB
>> data type in a SQL Server database?
>>
>> Br.
>> Mikael
>>
>>
>>
>> .
>>


 | 
Pages: 1
Prev: 80 Compatibility Mode
Next: junction table query