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/[/url]
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