Why is my SP_HELPINDEX showing a lot of indexes?

Question

Using sp_helpindex My_table I have this list:

But my table has only these indexes:

Why is this happening? I'm using a query to search for "equal indexes" in a particular table. This query is returning a LOT of indexes that I don't even have in the table.

WITH IndexColumns AS (
SELECT '[' + s.Name + '].[' + T.Name + ']' AS TableName,
i.name AS IndexName,  C.name AS ColumnName, i.index_id,ic.index_column_id,
COUNT(*) OVER(PARTITION BY t.OBJECT_ID, i.index_id) AS ColCount
FROM sys.schemas AS s
JOIN sys.tables AS t ON t.schema_id = s.schema_id
JOIN sys.indexes AS i ON I.OBJECT_ID = T.OBJECT_ID
JOIN sys.index_columns AS IC ON  IC.OBJECT_ID = I.OBJECT_ID
AND IC.index_id = I.index_id
JOIN sys.columns AS C ON  C.OBJECT_ID = IC.OBJECT_ID
AND C.column_id = IC.column_id
WHERE IC.is_included_column = 0
)
SELECT DISTINCT a.TableName, a.IndexName AS Index1, b.IndexName AS Index2
FROM IndexColumns AS a
JOIN IndexColumns AS b ON b.TableName = a.TableName
AND b.IndexName <> a.IndexName
AND b.index_column_id = a.index_column_id
AND  b.ColumnName = a.ColumnName
AND a.index_column_id < 3
AND a.index_id < b.index_id
AND a.ColCount <= B.ColCount
ORDER BY a.TableName, a.IndexName;

Where are these indexes coming from?

Answer 1

The indexes you are seeing are hypothetical indexes created by Database Engine Tuning Advisor.

DTA leaves a bit of a mess behind if it crashes or if you kill it because it runs too long.

They all include _dta_ in the name and you can see the hypothetical in the description.

Try the query from Brent as you will most likely have some of these on other tables too.

WITH hi AS (
SELECT QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(i.[object_id])) AS [Table] , QUOTENAME([i].[name]) AS [Index_or_Statistics], 1 AS [Type]
FROM sys.[indexes] AS [i]
JOIN sys.[objects] AS [o]
ON i.[object_id] = o.[object_id]
WHERE 1=1 
AND INDEXPROPERTY(i.[object_id], i.[index_id], 'IsHypothetical') = 1
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1

UNION ALL

SELECT QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(o.[object_id])) AS [Table], QUOTENAME([s].[name]) AS [Index_or_Statistics], 2 AS [Type]
FROM sys.[stats] AS [s]
JOIN sys.[objects] AS [o]
ON [o].[object_id] = [s].[object_id]
WHERE [s].[user_created] = 0
AND [o].[name] LIKE '[_]dta[_]%'
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1
)
SELECT [hi].[Table] ,
       [hi].[Index_or_Statistics] ,
       CASE [hi].[Type] 
       WHEN 1 THEN 'DROP INDEX ' + [hi].[Index_or_Statistics] + ' ON ' + [hi].[Table] + ';'
       WHEN 2 THEN 'DROP STATISTICS ' + hi.[Table] + '.' + hi.[Index_or_Statistics] + ';'
       ELSE 'DEAR GOD WHAT HAVE YOU DONE?'
       END AS [T-SQL Drop Command]
FROM [hi]

You should delete those, they aren't used anyway

EDIT: Not sure why but the OP had issues with the query from Brent and this select worked on his environment

SELECT t2.NAME AS table_name
    ,t1.NAME AS index_name
FROM sys.indexes t1
INNER JOIN sys.tables t2 ON t2.object_id = t1.object_id
WHERE t2.NAME = 'MyTable'
    AND t1.NAME LIKE '%_dta_%'
    AND t1.is_hypothetical = 1