-- drop all non-PK indexes in the database my_database

use [my_database]

declare @index_name sysname
declare @index_id int
declare @sql_cmd nvarchar(512)

-- get the name of each index in 'table_name.index_name' format.
--
--    * ignore primary keys (indid=1) and system-defined indexes
--      ('_WA_foo') and only get indexes on user-defined tables
--      ('IsUserTable')
--
--    * the 'indid' column is not used but needs to be here
--      because of the ORDER BY
--
--    * the ORDER BY is probably not necessary but forces
--      the indexes to be dropped in a semi-logical order

declare c_index_info cursor for
   select object_name(id)+'.'+[name],indid from sysindexes 
   where objectproperty(id,'IsUserTable') = 1
   and not [name] like '_WA_%' and indid > 1
   order by object_name(id),indid desc

-- create a 'DROP INDEX' command for each index
-- and execute it

open c_index_info

fetch next from c_index_info into @index_name,@index_id
while @@FETCH_STATUS = 0
begin
   set @sql_cmd = 'DROP INDEX '+@index_name
   exec sp_executesql @sql_cmd
   fetch next from c_index_info into @index_name,@index_id
end

-- clean up

close c_index_info
deallocate c_index_info

quote:

---

Originally posted by GilaMonster

Your assumption that indexes with an id of 1 are primary keys is incorrect. Indexes with an ID of 1 are the clustered index.
...
Best way is probably to find the name of the primary key from sysobject where xtype='pk' and excluding indexes with those names.

---

quote:

---

Also, index ID of 255 is the LOB data (large object - text, ntext, image) in a table and also is not a droppable index

---

quote:

---

For your info, the _WA_sys entries aren't system-defined indexes. They are column statistics. SQL will never automatically create an index.

---