March 3, 2010 at 1:39 pm
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.
March 3, 2010 at 5:33 pm
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[/url]
Learn Extended Events
March 4, 2010 at 2:55 am
You can use the undocumented procedure described here:
Regards,
Sarabpreet Singh
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
October 20, 2015 at 11:50 am
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/%5B/url%5D
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
November 6, 2015 at 10:38 pm
Script to disable index:
ALTER Index indexname ON tablename.columnname DISABLE;
Script to enable index:
ALTER Index indexname ON tablename.columnname REBUILD;
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy