How do i find indexes of the columns in SQL server 2000??

How do i find indexes of the columns of all the tables of the
datbase.........most importantly in SQL server 2000

Thanks a lot(001frien@.gmail.com) writes:

Quote:

Originally Posted by

How do i find indexes of the columns of all the tables of the
datbase.........most importantly in SQL server 2000

Here is a query. It lists only the five first index column, but you can
easily augment it if needed.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks a lot Erland

Erland Sommarskog wrote:

Quote:

Originally Posted by

Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx