Posted by amonotod on 07/12/07 18:02

On Jul 11, 8:16 pm, Roy Harvey <roy_har...@snet.net> wrote:
> For the second query it was actually a simplification to put each
> index column on its own row.
>
> SELECT o.name,
> i.name, ik.keyno,
> c.name
> FROM sysobjects o
> JOIN sysindexes i ON i.id = o.id
> JOIN sysindexkeys ik ON ik.id = i.id
> AND ik.indid = i.indid
> JOIN syscolumns c ON c.id = ik.id
> AND c.colid = ik.colid
> WHERE i.indid BETWEEN 1 AND 254
> AND indexproperty(o.id, i.name, 'IsStatistics') = 0
> AND indexproperty(o.id, i.name, 'IsHypothetical') = 0
> ORDER BY o.name, i.name, ik.keyno
>

Roy,
Thanks much for the help, I appreciate it! I was wondering... Can
you tell me why the query only shows Primary Key indexes? For the
databases I tested on, there are many additional indices that were
created with "create index..." that do not show up. Also, which field
would I query for the uniqueness attribute?

Thanks again,
amonotod

• Next in forum: Re: Help convert an Oracle select on indexes to MSSQL...?
• Prev in forum: Re: Does a Full Backup include transaction logs
• Thread view: Re: Help convert an Oracle select on indexes to MSSQL...?

[Reply to this message]