Posted by frien on 11/13/06 02:36

Thanks a lot Erland


Erland Sommarskog wrote:
> Erland Sommarskog (esquel@sommarskog.se) writes:
> > Here is a query. It lists only the five first index column, but you can
> > easily augment it if needed.
>
> What was my INSERT-key up to last night? I missed to paste in the
> query I was talking about. Here it is:
>
> SELECT o.name, i.name,
> col1 = MIN (CASE ik.keyno WHEN 1 THEN c.name END),
> col2 = MIN (CASE ik.keyno WHEN 2 THEN c.name END),
> col3 = MIN (CASE ik.keyno WHEN 3 THEN c.name END),
> col4 = MIN (CASE ik.keyno WHEN 4 THEN c.name END),
> col5 = MIN (CASE ik.keyno WHEN 5 THEN c.name END)
> 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
> GROUP BY o.name, i.name
> ORDER BY o.name, i.name
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: 200611 to string 'Nov 2006'
• Prev in forum: Re: T-SQL Question
• Thread view: Re: How do i find indexes of the columns in SQL server 2000???

[Reply to this message]