Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER – Rebuild/ Enable all indexes (Including Nonclustered Columnstore Indexes)’

In my previous article, I wrote a script to disable all index of any particular database or you can disable any particular category (NONCLUSTERED COLUMNSTORE INDEXES ,HEAP, CLUSTERED INDEX, NONCLUSTERED INDEX, XML INDEX, SPATIAL INDEX) index as well. Given below is the script that can rebuild (enable) all indexes of any particular database or can rebuild (enable) any particular category index as well.

CREATE PROCEDURE dbo.[REBUILD_ALL_INDEXES_PROC]
@Type int
AS
BEGIN
DECLARE @Schema_TableName nvarchar(max)
DECLARE @Index_Type int
DECLARE @String varchar(max)
DECLARE @Fillfactor VARCHAR(3)
SET @Fillfactor=80

DECLARE IndexCursor CURSOR FOR

Select scma.[name] + '.'
+ Quotename(tbl.name) as [Schema_TableName]
,i.type
from sys.tables AS tbl
INNER JOIN sys.indexes AS i
ON (i.index_id > 0 AND i.is_hypothetical = 0)
AND (i.object_id=tbl.object_id)
INNER JOIN sys.schemas AS scma
ON tbl.schema_id=scma.schema_id
Where tbl.type='U' AND ((1=(CASE
WHEN @Type is Null THEN 1 ELSE 0 END)
OR i.type = @Type))
Order By scma.[name] + '.' + tbl.name

OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @Schema_TableName,@Index_Type

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Index_Type=6
	BEGIN
                --To rebuild columnstore index only
		SET @String = 'ALTER INDEX ALL ON ' + @Schema_TableName +
                ' REBUILD;'
    END
	ELSE
	BEGIN
                --To rebuild other indexes
		SET @String = 'ALTER INDEX ALL ON ' + @Schema_TableName +
                ' REBUILD WITH (FILLFACTOR = ' + @Fillfactor + '
                , SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);'
    END
	Print @String
	EXEC (@String)
	FETCH NEXT FROM IndexCursor INTO @Schema_TableName,@Index_Type
END
CLOSE IndexCursor
DEALLOCATE IndexCursor
END
GO
--SYNTAX
--EXEC dbo.[REBUILD_ALL_INDEXES_PROC] Index type

--TO REBUILD NONCLUSTERED COLUMNSTORE INDEXES ONLY
USE AdventureWorks2012
GO
EXEC dbo.[REBUILD_ALL_INDEXES_PROC] 6

-- TO REBUILD HEAP ONLY
USE AdventureWorks2012
GO
EXEC dbo.[REBUILD_ALL_INDEXES_PROC] 0
GO
-- TO REBUILD CLUSTERED INDEX ONLY
USE AdventureWorks2012
GO
EXEC dbo.[REBUILD_ALL_INDEXES_PROC] 1
GO
-- TO REBUILD NONCLUSTERED INDEX ONLY
USE AdventureWorks2012
GO
EXEC dbo.[REBUILD_ALL_INDEXES_PROC] 2
GO
-- TO REBUILD XML INDEX ONLY
USE AdventureWorks2012
GO
EXEC dbo.[REBUILD_ALL_INDEXES_PROC] 3
GO
-- TO REBUILD SPATIAL INDEX ONLY
USE AdventureWorks2012
GO
EXEC dbo.[REBUILD_ALL_INDEXES_PROC] 4
GO
--TO REBUILD ALL INDEXES
USE AdventureWorks2012
GO
EXEC dbo.[REBUILD_ALL_INDEXES_PROC] NULL
GO

Read Full Post »