"Simon Hayes" <sq*@hayes.ch > wrote in message news:<41******* ***@news.bluewi n.ch>...
"crystal1" <cr******@spaml ess.net> wrote in message
news:ci******** @enews3.newsguy .com... 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_MSforeachta ble
@command1="prin t '?'" + a DBCC which just targets the ITW indexes (if such
a thing exists). Any ideas how to go about this?
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
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.i d) + '].[' + 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, 'IsAutoStatisti cs') = 0)))
EXEC(@strSQL)
FETCH NEXT FROM ITW_Stats INTO @objid, @indid
END
CLOSE ITW_Stats
DEALLOCATE ITW_Stats
Thank you
Raju
