SQL Server Disable Indexes and Rebuild Indexes dynamically

05 January,2012 by Jack Vamvas

To generate the ALTER INDEX..DISABLE  and  the ALTER INDEX..REBUILD statements for all nonclustered indexes for a single table use the following sql statements. 

Copy and Paste the statements , execute on the database. I’ve included the URL column  for easy click through to some notes on the code

1) Generate the ALTER INDEX..DISABLE statements

SELECT  
'ALTER INDEX [' +  sys.indexes.name + '] ON [' + sys.objects.name + '] DISABLE;' +CHAR(13)+CHAR(10) as EXECUTE_STATEMENT,
'http://www.sqlserver-dba.com/2012/01/sql-server-disable-index.html' as URL

FROM    sys.indexes         JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id 
WHERE   sys.indexes.type_desc = 'NONCLUSTERED'   
AND sys.objects.type_desc = 'USER_TABLE' 
AND sys.objects.name = 'MyTable'

 

2) Generate the ALTER INDEX..REBUILD statements

 

SELECT  
'ALTER INDEX [' +  sys.indexes.name + '] ON ' + sys.objects.name + ' REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
;' +CHAR(13)+CHAR(10) as EXECUTE_STATEMENT,
'http://www.sqlserver-dba.com/2012/01/sql-server-rebuild-index.html'> as URL

FROM    sys.indexes         JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id 
WHERE   sys.indexes.type_desc = 'NONCLUSTERED'   
AND sys.objects.type_desc = 'USER_TABLE' 
AND sys.objects.name = 'MyTable'

 Related Posts

SQL SERVER REBUILD INDEX

SQL SERVER DISABLE INDEX


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server Disable Indexes and Rebuild Indexes dynamically


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer