Just curious if anyone has a script to find and delete all indexes
created by index tuning wizard, leaving the original indexes untouched.
All of the original indexes in this particular database are preceded
with IX_, whereas those created by ITW are the table name followed by a
number.

I'm thinking of something along the lines of "sp_MSforeachtable
@command1="print '?'" + a DBCC which just targets the ITW indexes (if

[quoted text, click to view]

You could execute the output of a query like this (see "sysindexes" in Books
Online):

select 'drop index ' + object_name(id) + '.' + name
from sysindexes
where indid not in (0,255)
and name like object_name(id) + '[0-9]%'

Simon

[quoted text, click to view]

[quoted text, click to view]


I guess you are talking about Hypothetical Indexes.
Here is the script to drop the hypothetical indexes.

DECLARE @strSQL nvarchar(1024)
DECLARE @objid int
DECLARE @indid tinyint
DECLARE ITW_Stats CURSOR FOR SELECT id, indid FROM sysindexes WHERE
name LIKE 'hind_%' ORDER BY name
OPEN ITW_Stats
FETCH NEXT FROM ITW_Stats INTO @objid, @indid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SELECT @strSQL = (SELECT case when INDEXPROPERTY(i.id, i.name,
'IsStatistics') = 1 then 'drop statistics [' else 'drop index [' end +
OBJECT_NAME(i.id) + '].[' + i.name + ']'
FROM sysindexes i join sysobjects o on i.id = o.id
WHERE i.id = @objid and i.indid = @indid AND
(INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 1 OR
(INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 AND
INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics') = 0)))
EXEC(@strSQL)
FETCH NEXT FROM ITW_Stats INTO @objid, @indid
END
CLOSE ITW_Stats
DEALLOCATE ITW_Stats

Thank you