set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
ALTER PROCEDURE [dbo].[RebuildAllIndexes]
AS
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @indexname sysname;
DECLARE @maxdop tinyint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @infomessage varchar(1000);
DECLARE @fillfactor tinyint;
DECLARE @returncode tinyint;
DECLARE @dbname sysname;
DECLARE @localerror integer;
DECLARE @ExcludeTableList varchar(100)
 
SET @ExcludeTableList = ''+','
SET @dbname = DB_NAME()
 
-- Set MAXDOP default value
IF (@maxdop IS NULL)
BEGIN
SET @maxdop = (SELECT CONVERT(smallint, value) FROM master.sys.configurations (NOLOCK) WHERE name = 'max degree of parallelism')
END
SET @returncode = 0
 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
IF OBJECT_ID('tempdb..#work_to_do') IS NOT NULL
BEGIN
DROP TABLE #work_to_do
END
CREATE TABLE #work_to_do(
[objectid] [int] NULL,
[indexid] [int] NULL,
[fill_factor] [int] NOT NULL
)
-- Get indexes that meet our criteria
INSERT INTO #work_to_do
SELECT o.object_id,i.index_id, CASE i.fill_factor WHEN 0 THEN 100 ELSE i.fill_factor END AS fill_factor
FROM sys.indexes i (NOLOCK) INNER JOIN sys.objects o(nolock) ON i.object_id = o.object_id
WHERE i.index_id > 0 AND o.type ='U' AND CHARINDEX(o.name+',',@ExcludeTableList) = 0
ORDER BY O.name,i.index_id
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @fillfactor; --@partitionnum, @frag, ;
IF @@FETCH_STATUS < objectname =" QUOTENAME(o.name)," schemaname =" QUOTENAME(s.name)" schema_id =" o.schema_id" object_id =" @objectid;" schemaname =" '[sys]')" indexname =" QUOTENAME(name)" object_id =" @objectid" index_id =" @indexid;" command =" N'ALTER" command =" @command" command =" @command" fillfactor =" '" command =" @command" maxdop =" '" command =" @command" infomessage =" 'Executing" localerror =" @@ERROR"> 0
BEGIN
SET @infomessage = 'Reindex failed with error ' + CAST(@localerror AS varchar(10)) + '.'
RAISERROR (@infomessage, 11, 1) WITH LOG, NOWAIT
SET @returncode = @returncode + 1
END
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
RETURN @returncode