double indexes

Hi there,
I want to check if my tables have double indexes on the same column. Is there a select statement that I can do on the sysindexes?
Regards, Salsa

Keep in mind you're using systemobjects, so don't build software on it because it may change with every version/sp/hotfix ...
check out sysindexkeys to start with.
There is also the supported sp_helpindex []

Really, does it change that much?
there are 4 columns in this table, so with statement I'm getting the doubles?
so if colid and keyno have the same value, is this a double index?
Thanks, Salsa

While sysindexkeys is probably relevant, by itself it's just a list of ID numbers that doesn't tell you much.
Run sp_helpindex <table_name> to get all the indexes on a table, together with a list of the columns in the index. You then compare those lists yourself to see if a column is listed for more than one index.
Note that if a column is listed after the first column in an index, and the same column also has a separate index to it, that the separate index is appropriate if you do searches on this column by itself, where the multi-column index would be less helpful.

If it concerns indexes with only one colum, and the index column sorting (ascending/descending) is equal, then you have a duplicate idex.
With composit indexes, column order in the index is important !
Don't just drop indexes. Script the table with the indexes and investigate the statstics.
Some indexes are created because of foreign-key-constraints ! (exact column order with the relevant PK is needed to support deletes of parent table rows if DRI is active)

thanks!

If you are in 200, here's a script I stole from somewhere on the net:CREATE VIEW vw_index_list AS
SELECT
tbl.[name] AS TableName,idx.[name] AS IndexName,
INDEX_COL( tbl.[name], idx.indid, 1 ) AS col1,INDEX_COL( tbl.[name], idx.indid, 2 ) AS col2,
INDEX_COL( tbl.[name], idx.indid, 3 ) AS col3,INDEX_COL( tbl.[name], idx.indid, 4 ) AS col4,
INDEX_COL( tbl.[name], idx.indid, 5 ) AS col5,INDEX_COL( tbl.[name], idx.indid, 6 ) AS col6,
INDEX_COL( tbl.[name], idx.indid, 7 ) AS col7,INDEX_COL( tbl.[name], idx.indid, 8 ) AS col8,
INDEX_COL( tbl.[name], idx.indid, 9 ) AS col9,INDEX_COL( tbl.[name], idx.indid, 10 ) AS col10,
INDEX_COL( tbl.[name], idx.indid, 11 ) AS col11,INDEX_COL( tbl.[name], idx.indid, 12 ) AS col12,
INDEX_COL( tbl.[name], idx.indid, 13 ) AS col13,INDEX_COL( tbl.[name], idx.indid, 14 ) AS col14,
INDEX_COL( tbl.[name], idx.indid, 15 ) AS col15,INDEX_COL( tbl.[name], idx.indid, 16 ) AS col16,
dpages,
used,
rowcntFROM
SYSINDEXES idxINNER
JOIN SYSOBJECTS tbl ON idx.[id] = tbl.[id]WHERE indid > 0
AND INDEXPROPERTY( tbl.[id], idx.[name], 'IsStatistics') = 0GoSELECT l1.tablename, l1.indexname,
l2.indexname AS overlappingIndex, l1.col1,
l1.col2,
l1.col3,
l1.col4,
l1.col5,
l1.col6,
l1.col7,
l1.col8,
l1.col9,
l1.col10,
l1.col11,
l1.col12,
l1.col13,
l1.col14,
l1.col15,
l1.col16,
l1.dpages,
l1.used,
l1.rowcntFROM
vw_index_list l1 INNER JOIN vw_index_list l2 ON l1.tablename = l2.tablename
AND l1.indexname <> l2.indexnameAND l1.col1 = l2.col1
AND (l1.col2 IS NULL OR l2.col2 IS NULL OR l1.col2 = l2.col2)AND (l1.col3 IS NULL OR l2.col3 IS NULL OR l1.col3 = l2.col3)
AND (l1.col4 IS NULL OR l2.col4 IS NULL OR l1.col4 = l2.col4)AND (l1.col5 IS NULL OR l2.col5 IS NULL OR l1.col5 = l2.col5)
AND (l1.col6 IS NULL OR l2.col6 IS NULL OR l1.col6 = l2.col6)AND (l1.col7 IS NULL OR l2.col7 IS NULL OR l1.col7 = l2.col7)
AND (l1.col8 IS NULL OR l2.col8 IS NULL OR l1.col8 = l2.col8)AND (l1.col9 IS NULL OR l2.col9 IS NULL OR l1.col9 = l2.col9)
AND (l1.col10 IS NULL OR l2.col10 IS NULL OR l1.col10 = l2.col10)AND (l1.col11 IS NULL OR l2.col11 IS NULL OR l1.col11 = l2.col11)
AND (l1.col12 IS NULL OR l2.col12 IS NULL OR l1.col12 = l2.col12)AND (l1.col13 IS NULL OR l2.col13 IS NULL OR l1.col13 = l2.col13)
AND (l1.col14 IS NULL OR l2.col14 IS NULL OR l1.col14 = l2.col14)AND (l1.col15 IS NULL OR l2.col15 IS NULL OR l1.col15 = l2.col15)
AND (l1.col16 IS NULL OR l2.col16 IS NULL OR l1.col16 = l2.col16)ORDER
BYl1.tablename,
l1.indexnamego

Dear ndinakar,
Thank you so much for the script! Honestly I'm not very experienced with this stuff, so i'm looking at the script and i'm having some questions (anuone who now thinks this subject should be moved to the Beginner Forum, just say so).
Anyway what values should tbl.[name] and TableName have?
Regards, Salsa