Can anybody know how to remove all the indexes from database ?
+1
A:
Generate some sql querying the sysindexes table.
some thing along the lines of :
select 'drop index ' + i.name + ' on ' + o.name
from sysindexes i
inner join sysobjects o on i.id = o.id
where o.name <> i.name
The execute the results....
Preet Sangha
2009-07-11 10:57:40
+3
A:
Using this query will create you a list of DROP statements which you can then execute:
SELECT
'DROP INDEX ' + ix.name + ' ON ' + OBJECT_NAME(ID)
FROM
sysindexes ix
WHERE
ix.Name IS NOT null
That should be pretty fast and take care of dropping all indices :-)
Marc
PS: ah, sorry, I just noticed this will only work in SQL Server 2005 and up. For SQL Server 2000, you'll need to use the "sysindexes" view instead... I updated my statement accordingly
marc_s
2009-07-11 11:01:57
+1 Better than mine as I always forget OBJECT_XXX functions...
Preet Sangha
2009-07-11 11:03:08
in this table name is not coming it will not work any other script ?
KuldipMCA
2009-07-11 11:07:04
sorry i got it i solve the problem. i want script for sql2000it has some diff syntax from sql2005 so i got the answer.
KuldipMCA
2009-07-11 11:11:17
will this do the same thing: "select cmd from vw_drop_idnex" ?
djangofan
2009-08-28 00:10:31
@djangofan: I don't know any "vw_drop_index" view, so I can't really tell if that does the same thing......
marc_s
2009-08-28 07:19:31
this method may work but it doesnt account for dropping any contraints that might be necessary.
djangofan
2009-08-28 15:26:35
@djangofan: yes, good point - obviously, you might need to drop some constraints first in order to be able to drop all indices
marc_s
2009-08-28 15:28:09