identifying USER-DEFINED indexes in System Table

[btturner]

Trying to code a SQL 2k stored proc to list all "user defined' INDEXES in a SQL 2k DB.

Can I join SYSINDEXES to another system table to identify "user defined" indexes for a given DB?

 

[cmmrfrds]

I am sure there is a more elegant way, user tables are identified in sysobjects with xtype = 'U'. So you can join to sysobjects. A rough example.
SELECT i.*
FROM sysindexes i , sysobjects o
WHERE o.xtype = 'U' AND (i.[rows] > 0)
and i.id = o.id

 

[tlbroadbent]

I'm not sure what you mean by user defined indexes. I'll go along with cmmrfrds's interpretation and add a little to his query.

You'll want to exclude all rows in sysindexes where the name starts with '_WA_' or 'hind'. These are statistics entries created by SQL Server or the Index tuning wizard.

Query to list Indexes on User tables:

Select
TblName=o.name,
indexName=i.name
From sysindexes i
Join sysobjects o
On i.id=o.id
Where o.type='u'
And left(i.name,4) Not In ('_WA_', 'hind')
Order bY 1,2 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.