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

[001frien]

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

Thanks a lot

[Erland Sommarskog]

(00******@gmail.com) writes:

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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Erland Sommarskog (es****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[frien]

Thanks a lot Erland
Erland Sommarskog wrote:

Erland Sommarskog (es****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx