Reindex Stored Procedure

USE [master]
GO
/****** Object: StoredProcedure [dbo].[DbUtil_ALTER_INDEX] Script Date: 06/05/2008 16:18:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------------------------------------------------------------------------------------
-- OBJECT NAME : DbUtil_ALTER_INDEX
--
-- AUTHOR : Tara Kizer
-- : Alan T
--
-- DATE : February 27, 2007
-- : 2008 - 06- 06
--
-- INPUTS : @dbName - name of the database
-- @statsMode - mode to use for sys.dm_db_index_physical_stats
-- @defragType - REORGANIZE (INDEXDEFRAG) or REBUILD (DBREINDEX)
-- @minFragPercent - minimum fragmentation level
-- @maxFragPercent - maximum fragmentation level
-- @minRowCount - minimum row count
-- @PerformOnlineOnly - Forces to rebuild to be online for enterprise ed. without 1 = true
--
-- OUTPUTS : None
--
-- DEPENDENCIES : sys.dm_db_index_physical_stats, sys.objects,
-- sys.schemas, sys.indexes, sys.partitions
--
-- DESCRIPTION : Defragments indexes
/*
EXEC DbUtil_ALTER_INDEX
@dbName = 'DatabaseName',
@statsMode = 'SAMPLED',
@defragType = 'REBUILD',
@minFragPercent = 10,
@maxFragPercent = 100,
@minRowCount = 1000
, @PerformOnlineOnly = 1
*/
-------------------------------------------------------------------------------------------
CREATE PROC [dbo].[DbUtil_ALTER_INDEX]
(
@dbName sysname
, @statsMode varchar(8) = 'SAMPLED'
, @defragType varchar(10) = 'REORGANIZE'
, @minFragPercent int = 25
, @maxFragPercent int = 100
, @minRowCount int = 0
, @PerformOnlineOnly bit = 1
)
AS

SET NOCOUNT ON

DECLARE @i int
DECLARE @objectId int
DECLARE @objectName sysname
DECLARE @indexId int
DECLARE @indexName sysname
DECLARE @schemaName sysname
DECLARE @partitionNumber int
DECLARE @partitionCount int
DECLARE @recordCount bigint
DECLARE @sql nvarchar(4000)
DECLARE @edition int
DECLARE @parmDef nvarchar(500)

SELECT @i = 1, @edition = CONVERT(int, SERVERPROPERTY('EngineEdition'))

--Make sure the temp table does not exist
IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type = N'U' AND name like N'#FragIndex%') DROP TABLE #FragIndex;

SELECT
IDENTITY(int, 1, 1) AS FragIndexId,
[object_id] AS ObjectId,
index_id AS IndexId,
avg_fragmentation_in_percent AS FragPercent,
record_count AS RecordCount,
partition_number AS PartitionNumber
INTO #FragIndex
FROM sys.dm_db_index_physical_stats (DB_ID(@dbName), NULL, NULL, NULL, @statsMode)
WHERE avg_fragmentation_in_percent > @minFragPercent
AND avg_fragmentation_in_percent < @maxFragPercent AND index_id > 0
AND record_count > @minRowCount
ORDER BY ObjectId

SELECT
@objectId = ObjectId,
@indexId = IndexId,
@recordCount = RecordCount
FROM #FragIndex
WHERE FragIndexId = @i

WHILE @@ROWCOUNT <> 0
BEGIN
SET @sql = '
SELECT @objectName = o.[name], @schemaName = s.[name]
FROM ' + @dbName + '.sys.objects o
JOIN ' + @dbName + '.sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.[object_id] = @objectId'

SET @parmDef = N'@objectId int, @objectName sysname OUTPUT, @schemaName sysname OUTPUT'

EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId,
@objectName = @objectName OUTPUT, @schemaName = @schemaName OUTPUT

SET @sql = 'SELECT @indexName = [name]
FROM ' + @dbName + '.sys.indexes
WHERE [object_id] = @objectId AND index_id = @indexId AND is_disabled = 0'

SET @parmDef = N'@objectId int, @indexId int, @indexName sysname OUTPUT'

EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId, @indexId = @indexId,
@indexName = @indexName OUTPUT

SET @sql = 'SELECT @partitionCount = COUNT(*)
FROM ' + @dbName + '.sys.partitions WHERE [object_id] = @objectId AND index_id = @indexId'

SET @parmDef = N'@objectId int, @indexId int, @partitionCount int OUTPUT'

EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId, @indexId = @indexId,
@partitionCount = @partitionCount OUTPUT

SET @sql = 'ALTER INDEX [' + @indexName + '] ON [' + @dbName + '].[' +
@schemaName + '].[' + @objectName + '] ' + @defragType

-- Developer and Enterprise have the ONLINE = ON option for REBUILD
IF @defragType = 'REBUILD' AND @edition = 3 AND @PerformOnlineOnly = 1
SET @sql = @sql + ' WITH (ONLINE = ON)'
ELSE IF @defragType = 'REBUILD' AND @edition = 3 AND @PerformOnlineOnly = 0
SET @sql = @sql + ' WITH (ONLINE = OFF)'

IF @partitionCount > 1
SET @sql = @sql + ' PARTITION = ' + CAST(@partitionNumber AS varchar(10))

print @sql

BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
print 'ERROR1 : ' + cast(ERROR_LINE() as nvarchar(10));
print 'ERROR2 : ' + ERROR_MESSAGE();
END CATCH;


SET @i = @i + 1

SELECT
@objectId = ObjectId,
@indexId = IndexId,
@recordCount = RecordCount
FROM #FragIndex
WHERE FragIndexId = @i
END


IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type = N'U' AND name like N'#FragIndex%') DROP TABLE #FragIndex;