listing columns in an Index...

listing columns in an Index...

Post by John Biswel » Fri, 22 Jun 2001 18:53:46



Hi,

Does anyone know the best way to list columns that are used by a specified
index? I would like to do this using DMO but I've noticed that the
IndexedColumns property is write only. Perhaps there is an efficient way of
doing it using TSQL?

Thanks,

John.

 
 
 

listing columns in an Index...

Post by Dejan Sark » Fri, 22 Jun 2001 19:01:18


John,

Quote:> Does anyone know the best way to list columns that are used by a specified
> index? I would like to do this using DMO but I've noticed that the
> IndexedColumns property is write only. Perhaps there is an efficient way
of
> doing it using TSQL?

you can use the sp_helpindex and sp_help system stored procedures.

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.

 
 
 

1. Listing indexes and index columns in SQL

Hi,

How do I list all indexes with type 'Index' and index columns on a specific
table.

I have tried something like:
SELECT * from SysIndexes where Id = object_id('CONTACTTYPE')
for the indexes, but I also receive 'Unique' and 'Primary key'. I would like
only to receive indexes with type 'Index'.

For the keys have I tried
SELECT *, index_col('CONTACTTYPE', sysindexkeys.Indid, sysindexkeys.keyNo)
from sysindexkeys where Id = object_id('CONTACTTYPE') and indid=4

But I am not able to see the sort order of the column. Is that possible.

Best regards

Henrik Skak Pedersen

2. Ingres won't listen...!

3. Getting Clustered Index Column List

4. temporary lock database

5. Table Listing and Column Listing

6. Oracle Webserver + PL/SQL

7. Insert value list does not match column list.

8. Row Devices and Chunk offset

9. strange error need help - Column name appears more than once in resulting column list

10. Composite Indexes vs. Multiple Single-column indexes

11. Getting the Index names and indexed columns for a schema

12. details wanted on indexed views and indexed computed columns

13. Multi column index versus many indexes (7 vs 2000)