August 25, 2008 at 2:23 pm
Is there a script available to drop all indexes, pks, fks for all tables ? I've seen a few for tables only, nothing for all tables.
¤ §unshine ¤
August 26, 2008 at 6:37 am
try this script. Combine it with sp_msforeachtable to disable or enable indexes, depending on @switch
Note: this stored procedure will only disable nonclustered indexes or indexes which are added for performance considerations.
constraint and primarykey indexes are skipped.
create procedure usp_toggle_index(@objectname sysname, @switch bit = 0, @debug bit = 0, @verbose bit = 1) as
begin
declare @SQLCmd nvarchar(512)
declare @action nvarchar(16)
declare @counter int
set @counter = 0
if @switch = 0
set @action = ' disable;'
else
set @action = ' rebuild;'
declare c_toggle_index cursor for
select'alter index ' + QUOTENAME(name) + ' on ' + QUOTENAME(@objectname) + @action
fromsys.indexes
wheretype_desc = 'nonclustered'
andis_unique = 0
andis_primary_key = 0
andis_unique_constraint = 0
andis_disabled = @switch
andobject_id = object_id(@objectname)
openc_toggle_index
fetchnext
fromc_toggle_index
into@SQLCmd
while @@fetch_status = 0
begin
set @counter = @counter + 1
if @debug = 0
exec (@SQLCmd)
else
print @SQLCmd
fetch next
from c_toggle_index
into @SQLCmd
end
close c_toggle_index
deallocate c_toggle_index
if @verbose = 1
print case @switch
when 1 then '- rebuilding ' + cast(@Counter as varchar) + ' indexes for table ' + @objectname
else '- disabling ' + cast(@Counter as varchar) + ' indexes for table ' + @objectname
end
end
go
To disable indexes for all tables: exec sp_msforeachdb 'exec usp_toggle_index ''?'',0'
Make sure you're in the right database. execute with @debug=1 to see the actual statements, insted of executing these.
Wilfred
The best things in life are the simple things
August 26, 2008 at 7:49 am
I'm actually looking for something to drop/remove all indexes. But this is great for other projects.
¤ §unshine ¤
August 26, 2008 at 9:49 am
Here is one....
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
¤ §unshine ¤
August 26, 2008 at 10:02 am
are you sure you want to drop all indexes?
you have the scripts to re create them?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 26, 2008 at 10:20 am
Thank you!
¤ §unshine ¤
August 26, 2008 at 11:19 am
Warning DROP INDEX for a PK does *not* work! you must DROP the CONSTRAINT!
* Noel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply