Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Enable/Disable all indexes in all tables at once Expand / Collapse
Author
Message
Posted Wednesday, March 3, 2010 1:39 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 2:40 PM
Points: 759, Visits: 645
Hi,

Does any one have a script to ENABLE and DISABLE all the Indexes for all the Tables of a particular database on sql server 2005.
Post #876331
Posted Wednesday, March 3, 2010 5:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:02 PM
Points: 21,657, Visits: 15,326
ALIF-662928 (3/3/2010)
Hi,

Does any one have a script to ENABLE and DISABLE all the Indexes for all the Tables of a particular database on sql server 2005.



To disable
select 'alter index ' +i.name+ ' on ' +o.name+ ' disable ' + CHAR(13)+Char(10)+';'
From sys.indexes i
Inner Join sys.objects o
On o.object_id = i.object_id
Where o.is_ms_shipped = 0
And i.index_id >= 1

To enable - REBUILD the indexes.

Article to read:
http://www.sqlservercentral.com/articles/Indexing/63533/




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #876463
Posted Thursday, March 4, 2010 2:55 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 13, 2014 10:40 AM
Points: 368, Visits: 543
You can use the undocumented procedure described here:
http://solutions-guru.com/articles/sql-server/86-undocumented-procedure-spmsforeachtable-a-spmsforeachdatabase.html



Regards,
Sarabpreet Singh
SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek
Post #876664
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse