Sunday, April 15, 2007

Drop all indexes

Well, sometimes we have to do that. A large INSERT statement may run faster for instance. The below script just generate DROP command of all indexes in current dataabse. You can add a filter to specify a table name for example.(SQL Server 2000)

SELECT
'DROP INDEX ' +
QUOTENAME(USER_NAME(o.uid)) +
'.' +
QUOTENAME(o.name) +
'.' +
QUOTENAME(i.name)
FROM sysobjects o
JOIN sysindexes i ON
i.id = o.id
WHERE i.indid BETWEEN 1 AND 254 AND
INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 0 AND
INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 0 AND
OBJECTPROPERTY(o.id, 'IsMSShipped') = 0

5 comments:

Sammy said...

The script worked just fine. Thanks.

But there is one issue. i am getting the following error and thus unable to delete. any solution for it. I mean for all theindexes in the db.

"An explicit DROP INDEX is not allowed on index . It is being used for PRIMARY KEY constraint enforcement."

haiyan said...

Two years passed, I still remembered the scene of fighting against wolves in Tir Chonaill with friends, sharing fruit which was got by our mabinogi gold around a fire, fishing in a clean pond. Players will have five seconds freezed to make their own preparation, when the fight beings, win or lose, it depends on your own cheap mabinogi. Here is some mabinogi money I took months ago; I was the guy wielding a thick sword. And I know it is rude enough to beat a woman. But, we can use some mabinogi online gold to edit some dates in the setup menu to change the clothes we wear. As a kind of fantastic online game, I think Mabinogi is more similar to comic and need to buy mabinogi gold.

Replica Watches said...

29047126483369175 I play dofus Replica Watches for one year, I Replica Rolex Watches want to get some Replica Watch kamas to buy Replica Chanel Watches item for my character. So, I search "Replica Swiss Watches" on google and found many website. As Exact Replica Graham Watch the tips from the forum, I just review the Swiss Replica Watches websites and choose some Replica Montblanc Watches quality sites to Replica Cartier Watches compare the price, and go to their Replica Breguet Watches online support to make Replica Breitling Watches the test. And Last Chaos Gold I decide to use Replica BRM Watch at the end. And Tag Heuer Replica Watch that is the Replica IWC Watch beginning..

aiya said...
This comment has been removed by a blog administrator.
aiya said...
This comment has been removed by a blog administrator.