Retrieving Index references using Information_Schema 1

[gradley]

Does anyone know how to retrieve the index information (clustered and non-clustered) for a table using INFORMATION_SCHEMA? I know that Microsoft wants you to stay away from system tables such as sysindexes but I cannot determine how to retrieve this information through the schemas.

 

[nigelrivett]

There are a lot of things that aren't available via those views. I think this is one of those that you have to use sysindexes for.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[jitter]

Depending on how much information you need you can use sp_helpindex to retrieve basic information on the indexes for the table. You may have tried this though and not gotten the information that you need.

jitter

 

[gradley]

Actually this is one procedure I hadn't looked at. It's not perfect but it allows me to extract the seqeuce of the fields in each of the indexes.

Thank you!

 

[nigelrivett]

Thought you wanted to do it using the information_schema views?
If you are going to use sp_helpindex you may as well use the system tables directly.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.