Drop All database indexes

How can I drop all the indexes in all the tables in a MS 2000 SQL Server?select name from the sysindexes table for all user tables, concatenate with 'drop index '. and execute it. Not very simple yet dangerous too.|||Originally posted by freddyb
How can I drop all the indexes in all the tables in a MS 2000 SQL Server?

select case when sop.xtype in('PK','UQ')
then
'alter table '+so.name+' drop constraint '+sy.name+char(13)+'go'
else
'drop index '+so.name+'.'+sy.name+char(13)+'go'
end
from sysindexes sy
join sysobjects so on so.id=sy.id and so.xtype='U'
left join sysobjects sop on sop.parent_obj=so.id and sop.name=sy.name
where sy.indid not in(0,255)|||this isnt another I want to do it and DO IT NOW! deal is it ?|||Originally posted by freddyb
How can I drop all the indexes in all the tables in a MS 2000 SQL Server? I'm just being curious, but why on earth would you want to? If you want to shoot yourself in the foot, go outside and get it over with quickly... Dropping all the incidies will get you to the same point, but it seems like it would be a lot more agonizing in the process.

-PatP|||pat
you know what a really cool way to do this is
when you generate a database script, justs choose indexes and indicate that you want the script to contain the tests for existence and the drop clauses.

then on the last page of the dialog tell it to create 1 script for each object
imagine 100+ indexes

Yee Haw|||Originally posted by Ruprect
pat
you know what a really cool way to do this is
when you generate a database script, justs choose indexes and indicate that you want the script to contain the tests for existence and the drop clauses.

then on the last page of the dialog tell it to create 1 script for each object
imagine 100+ indexes

Yee Haw I must have "missed a meeting" somewhere. This is a good thing?

-PatP|||I think freddyb is somehow related to the other guy who was trying to "DROP ALL TABLES IN ONE SHOT".|||Hello! McFLy.. Pay Attention, McFly!

this isnt another I want to do it and DO IT NOW! deal is it ?

McFLy, You Irish Bug!|||Scott does...DTS