September 5, 2003 at 8:26 am
does anone have a scipt to drop all indexes, then add them back?
September 5, 2003 at 9:38 am
Try following script to generate drop indexes statements from all user tables.
select 'drop index ' + object_name(ind.id) + '.' + ind.name
from sysindexes ind, sysobjects obj
where ind.id = obj.id
and obj.xtype = 'U'
and ind.name not like '_WA%'
and ind.indid <> 0
and ind.indid <> 1
and ind.indid <> 255
Before drop the indexes, you can generate the indexe creation script from EM.
September 5, 2003 at 1:33 pm
-- Drop all NCI's(excluding any PK unique)
select 'drop index ' + object_name(ind.id) + '.' + ind.name
from sysindexes ind, sysobjects obj
where ind.id = obj.id
and obj.xtype = 'U'
and obj.type <> 'K'
and ind.name not like '_WA%'
and ind.name not like coalesce((select constraint_name from information_schema.table_constraints where constraint_name = ind.name), '')
and ind.indid > 1 and ind.indid < 255
order by obj.name
September 5, 2003 at 1:47 pm
For the sake of completeness...
This code will script out the drop index for all non-clustered indexs(excluding any unique primary key indexes).
DROP INDEX...
select 'drop index ' + object_name(ind.id) + '.' + ind.name
from sysindexes ind, sysobjects obj
where ind.id = obj.id
and obj.xtype = 'U'
and obj.type <> 'K'
and ind.name not like '_WA%'
and ind.name not like coalesce((select constraint_name from information_schema.table_constraints where constraint_name = ind.name), '')
and ind.indid > 1 and ind.indid < 255
order by obj.name
CREATE INDEX...
This above result set will match the "Generate SQL Script" for "Script Indexes" only. (remove the clustered indexes that are scripped out and you have a match.)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply