Find fields with an index

Hi.
I am trying to find a way to establish whether a field has an index on it or
not. This is relevant because I am writing code to loop through the fields i
n
a table and shrink columns according to the length of the longest item in
them, and you cannot alter a column that has an index on it. This is
terminating the loop whenever I hit an indexed field.
Cheers,
NeilNeil
Something like this
SELECT OBJECT_NAME(id) AS table_name,name,
COUNT(NULLIF(NULLIF(indid,0),255)) AS num_of_indexes
FROM sysindexes
WHERE OBJECTPROPERTY(id,'IsUserTable')=1
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id,name, 'IsHypothetical') = 0
GROUP BY id,name
order by table_name
"NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
news:7B353578-5048-462C-AD61-51D160999687@.microsoft.com...
> Hi.
> I am trying to find a way to establish whether a field has an index on it
> or
> not. This is relevant because I am writing code to loop through the fields
> in
> a table and shrink columns according to the length of the longest item in
> them, and you cannot alter a column that has an index on it. This is
> terminating the loop whenever I hit an indexed field.
> Cheers,
> Neil
>
>|||Thanks Uri.
Unfortunately, I still need to find the fields that the indexes are on.
Given the slgihtly shoddy index naming protocol we are using, it's ly not
jsut a case of stripping off a substring of the index name.
Is there an easy way to find the keys for the index?
Aha! Just found the sysindexkeys table. Feel a bit daft.
Thanks for your help.
Neil
Cheers,
Neil
"Uri Dimant" wrote:

> Neil
> Something like this
> SELECT OBJECT_NAME(id) AS table_name,name,
> COUNT(NULLIF(NULLIF(indid,0),255)) AS num_of_indexes
> FROM sysindexes
> WHERE OBJECTPROPERTY(id,'IsUserTable')=1
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
> INDEXPROPERTY(id,name, 'IsHypothetical') = 0
> GROUP BY id,name
> order by table_name
>
> "NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
> news:7B353578-5048-462C-AD61-51D160999687@.microsoft.com...
>
>|||Hi Jones
Probably, this can help you.
select object_name(sysindexes.id),
index_col(object_name(sysindexes.id),indid,1)
from sysindexes
INNER JOIN sysobjects on sysobjects.id = sysindexes.id
where
sysobjects.xtype in ('u','v') and
sysindexes.status = 0 and
index_col(object_name(sysindexes.id),indid,1) IS NOT NULL
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"NeilDJones" wrote:
> Thanks Uri.
> Unfortunately, I still need to find the fields that the indexes are on.
> Given the slgihtly shoddy index naming protocol we are using, it's ly n
ot
> jsut a case of stripping off a substring of the index name.
> Is there an easy way to find the keys for the index?
> Aha! Just found the sysindexkeys table. Feel a bit daft.
> Thanks for your help.
> Neil
> Cheers,
> Neil
> "Uri Dimant" wrote:
>|||Thanks Chandra, that does it nicely.
I found the details this way instead:
select a.name, b.name from sysobjects a
inner join syscolumns b on a.id = b.id
inner join sysindexkeys c on b.colid = c.colid and a.id = c.id
where a.name = 'tableA'
Cheers,
Neil
"Chandra" wrote:
> Hi Jones
> Probably, this can help you.
>
> select object_name(sysindexes.id),
> index_col(object_name(sysindexes.id),indid,1)
> from sysindexes
> INNER JOIN sysobjects on sysobjects.id = sysindexes.id
> where
> sysobjects.xtype in ('u','v') and
> sysindexes.status = 0 and
> index_col(object_name(sysindexes.id),indid,1) IS NOT NULL
> please let me know if u have any questions
>
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "NeilDJones" wrote:
>|||Yes jones
quite interesting! yes ur solution is correct. i tried for colid and
couldnot find it.
then, i checked how sp_help was doing it.
sp_help uses sp_helpindex,
and i got the code sniplett from that SP
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"NeilDJones" wrote:
> Thanks Chandra, that does it nicely.
> I found the details this way instead:
> select a.name, b.name from sysobjects a
> inner join syscolumns b on a.id = b.id
> inner join sysindexkeys c on b.colid = c.colid and a.id = c.id
> where a.name = 'tableA'
> Cheers,
> Neil
> "Chandra" wrote:
>