SQL Server Disable Indexes and Rebuild Indexes dynamically

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