how do I get the list of all indexes created on table in a particular database

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Christopher Klein SSCrazy Points: 2648 More actions · Answer 1

I could only get part of it, dont know where to find the actual index names

SELECT DISTINCT

= OBJECT_NAME(OBJECT_ID), 'CLUSTERED'

FROM SYS.INDEXES

WHERE [TYPE]=1 and OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1

UNION

SELECT DISTINCT

= OBJECT_NAME(OBJECT_ID),'NONCLUSTERED'

FROM SYS.INDEXES

WHERE [TYPE]=2 and OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1

ORDER BY

type 1 is CLUSTERED, 2 is NONCLUSTERED and 0 would mean there is no index on the table.

somehow you should be able to join in the table 'sysindexes' (no dot) to get the names

satish bobba-490352 Old Hand Points: 366 More actions · Answer 2

Old Hand

Points: 366

October 10, 2007 at 3:38 pm

Thank you very much

satish bobba-490352 Old Hand Points: 366 More actions · Answer 3

I need to get the column name on which these indexes are defined.