469,106 Members | 2,267 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

Removing Tuning Wizard Indexes

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
such a thing exists). Any ideas how to go about this?
Jul 20 '05 #1
3 2374

"crystal1" <cr******@spamless.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_MSforeachtable
@command1="print '?'" + 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
Jul 20 '05 #2
"Simon Hayes" <sq*@hayes.ch> wrote in message news:<41**********@news.bluewin.ch>...
"crystal1" <cr******@spamless.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_MSforeachtable
@command1="print '?'" + 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.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
Raju
Jul 20 '05 #3
Simon Hayes wrote:
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


Excellent. Thank you.
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Maryam | last post: by
2 posts views Thread by SAP BASIS Consultant | last post: by
2 posts views Thread by Ed L. | last post: by
2 posts views Thread by rAinDeEr | last post: by
reply views Thread by uzi | last post: by
reply views Thread by Medhatithi | last post: by
3 posts views Thread by Sam Durai | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.