list of clustered index in the database

[bala]

hi guru's

would appreciate if someone could show how to list all the clustered
indexes in the database.

if it can done as a output of single query it would be fine. the output
should be the table name, column name and clustered index name.

thanx
bala

[Stu]

I don't think it can be done in a single query, but you can do it like
so:

--declare variables and temp table for accumulation
DECLARE @tName varchar(200)
CREATE TABLE #t (table_name varchar(200),
index_name varchar(200),
index_description varchar(210),
index_keys nvarchar(2078))

--open cursor for user tables
DECLARE C CURSOR LOCAL FOR
SELECT name
FROM sysobjects
WHERE xtype = 'U'

OPEN C

FETCH NEXT FROM c INTO @tname

WHILE @@FETCH_STATUS = 0
BEGIN

--run sp_helpindex against table in cursor
INSERT INTO #t (index_name, index_description, index_keys)
exec sp_helpindex @tname

--since sp_helpindex doesn't return a table name,
--have to update the current NULL table_name
UPDATE #t
SET table_name = @tname
WHERE table_name is NULL

--Loop by getting next row from cursor
FETCH NEXT FROM c INTO @tname
END

CLOSE C

DEALLOCATE C

--retrieve specified data; limit it to clustered indexes
SELECT table_name, index_name, index_keys
FROM #t
WHERE index_description like 'clustered%'

DROP TABLE #t

HTH,
Stu

[bala]

hey stu

thanx for the quick response. will try it out tomorrow in office

regards
bala

[Jagged]

This should work too.

select object_name(id) as table_name, name as index_name
from sysindexes
where indid = 1

[Erland Sommarskog]

bala (ba*****@gmail.com) writes:

would appreciate if someone could show how to list all the clustered
indexes in the database.

if it can done as a output of single query it would be fine. the output
should be the table name, column name and clustered index name.

Here is a query:

SELECT tblname = CASE WHEN ik.keyno = 1 THEN o.name ELSE '' END,
ixname = CASE WHEN ik.keyno = 1 THEN i.name ELSE '' END,
ik.keyno, colname = c.name,
isdesc = CASE indexkey_property(o.id, i.indid, ik.keyno,
'IsDescending')
WHEN 1 THEN 'DESC'
ELSE ''
END
FROM sysobjects o
JOIN sysindexes i ON o.id = i.id
JOIN sysindexkeys ik ON i.id = ik.id
AND i.indid = ik.indid
JOIN syscolumns c ON c.id = ik.id
AND c.colid = ik.colid
WHERE i.indid = 1
ORDER BY o.name, ik.keyno

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Stu]

Ahhhh; I didn't even see the sysindexkeys table. I tried doing it with
INDEX_COL(), but it was a miserable failure.

Stu

[bala]

thanx guys. the pointer towards the right direction is much appreciated

bala