Retrieving indexes info

[Paul_]

Hi,

Is there any way to retrieve index information for a database through a SQL query?

I usually query out tables structure, et al using INFORMATION_SCHEMA. Is there anything like that for indexes?

Thanks,
Paul

[skhanal]

I don't think you can get that from Information schema views, you can run

sp_helpindex <tablename>

to get details. Or query sysindexes table.

[Paul_]

Thank you skhanal.

How could I query sysindexes to get a set of fields like this:

-Index name
-Table name
-Index keys
-Kind of index

?

Thanks again.

[rmiao]

You can't get all of them from sysindexes, books online lists columns in sysindexes.

[Paul_]

You can't get all of them from sysindexes, books online lists columns in sysindexes.

Thanks rmiao, I'll be using sp_helpindex <tablename> then...

[skhanal]

You can look under the hood of sp_helpindex to get an idea of how sql server gets it and write your own query.

[Paul_]

That's a neat idea. How can I see it? I'm using MSSQL 97/2000.

[nosepicker]

Somebody posted this query on another forum:

SELECT LEFT(so.[name], 30) TableName, LEFT(si.[name], 50) IndexName, LEFT(sc.[name], 30) ColumnName, si.indid,
CASE si.indid WHEN 1 THEN 'Clustered' ELSE 'Non-Clustered' END IndexType, LEFT(sfg.groupname, 10) GroupName
FROM sysindexes si
INNER JOIN sysindexkeys sik ON (si.[id] = sik.[id] AND si.indid = sik.indid)
INNER JOIN sysobjects so ON si.[id] = so.[id]
INNER JOIN syscolumns sc ON (so.[id] = sc.[id] AND sik.colid = sc.colid)
INNER JOIN sysfilegroups sfg ON si.groupid = sfg.groupid
--where si.indid between 2 and 254 --only non-clustered indexes
--WHERE si.indid = 1 --only non-clustered indexes
WHERE si.[name] NOT LIKE 'sys%' --filter out tables that start with sys
AND si.[name] NOT LIKE '[u,n]c%sys%' --filter out tables that start with ncsys, nc1, nc2, ucsys
AND si.[name] NOT LIKE '_WA_%' --filter out tables that start with _WA_
AND si.[name] NOT LIKE 'hind_%' --filter out tables that start with hind_
AND so.[name] <> 'dtproperties' --filter out the dtproperties table
--AND sfg.groupname = 'PRIMARY'
ORDER BY so.[name], si.indid

[Paul_]

It works ok. Thank you!