September 13, 2006 at 2:52 pm
Hi, I need some help.
I need a sql script to automatically drop all existing non-clustered indexes in a database. Also I will execute this script thru a jdbc driver. So any help is greatly appreciated.
Have a blessed day.
Thanks
September 13, 2006 at 3:23 pm
Use this procedure to delete all non clustered indexes in the database
Create procedure dropIndex as
Begin
declare @tab sysname,
@idx sysname,
@sql nvarchar(4000)
declare cur cursor for
select o.name as tab ,i.name as idx from sysindexes i inner join sysobjects o on i.id=o.id
where (i.indid>1 and i.indid<255) and o.xtype='U' and i.name not like '_WA_Sys_%'
open cur
fetch next from cur into @tab,@idx
while @@fetch_status=0
begin
set @sql='drop index '+@tab+'.'+@idx
print @sql
fetch next from cur into @tab,@idx
end
close cur
deallocate cur
End
September 13, 2006 at 4:12 pm
Thank you for your prompt help. Just a side note, on the where clause why do you include the clause "i.name not like '_WA_Sys_%'?
September 13, 2006 at 4:34 pm
These are the SQL Server generated statistics for the tables. Statistics generated tables will have a row in sysindexes table
September 14, 2006 at 11:07 am
I've tried running it, to most part it works, but it gives an error because it cannot drop a unique constraint. For example I have a table that a column is unique and it gives an error message of.
Error Code: 3723, SQL State: HY000] [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]An explicit DROP INDEX is not allowed on index 'system_sequence.UQ__system_sequence__603A4C29'. It is being used for UNIQUE KEY constraint enforcement.
How do you modify the script to exclude this unique constraint index?
September 14, 2006 at 11:31 am
you can replace the select statement for cursor with this
select
o.name as tab ,i.name as idx,i.status from sysindexes i inner join sysobjects o on i.id=o.id where (i.indid>1 and i.indid<255) and o.xtype='U' and i.name not like '_WA_Sys_%' and i.name not like 'UQ_%'
I am searching a better way to do this
September 14, 2006 at 12:07 pm
This is the way to get those without hardcoding names:
select OBJECT_NAME(i.id) as tab , i.name as idx
from sysindexes i
where i.indid between 1 and 249
and INDEXPROPERTY(i.id, i.name, 'IsClustered') = 0
and INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 0
and INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics') = 0
and INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 0
and OBJECTPROPERTY(i.id,'IsMSShipped') = 0
and not exists (select *
from sysobjects o
where xtype = 'UQ'
and o.name = i.name )
* Noel
September 14, 2006 at 12:13 pm
Its exactly what Jun wanted
September 14, 2006 at 12:17 pm
Thank you for both of you. You have save me a lot of pain and man hours. It is so nice to have a very active and vibrate community/forum. Keep up the good works you two.
Have a blessed day.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply