Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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, April 27, 2016 11:53 AM
Points: 765, Visits: 743
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 11:25 PM
Points: 19,958, Visits: 18,189
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
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, April 1, 2016 11:24 AM
Points: 368, Visits: 558
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
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
Post #876664
Posted Tuesday, October 20, 2015 11:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, June 12, 2016 8:24 AM
Points: 29, Visits: 304
Edit: DOH, I didn't see this thread was quite old, for some reason it came up in my feed on the site home page. Oh well, still relevant certainly!

Hi there, I actually just published an article describing exactly how to do this, with a stored procedure that wraps up all the necessary logic.

http://www.sqlservercentral.com/articles/Maintenance+and+Management/131604/

Let me know if this works for you or if you can think of anything that would make it better!


Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software

Follow me at http://sqljosh.com
Post #1729419
Posted Friday, November 6, 2015 10:38 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 7, 2016 11:26 PM
Points: 221, Visits: 260
Script to disable index:
ALTER Index indexname ON tablename.columnname DISABLE;

Script to enable index:
ALTER  Index indexname ON tablename.columnname REBUILD; 


Post #1734599
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse