Stabilize Index

This sp is used to update index in a database. This sp has got three optional varchar parameters.
First parameter will take the tablename. Second parameter will take the indexname. Third parameter will take the fillfactor.

The following option we can execute the sp in a user database.

1. exec StabilizeIndex
It will update all index in a user database

2. StabilizeIndex 'TableName'
It will update all index in a table

3. StabilizeIndex 'TableName', 'IndexName'
It will update an index in a table.

4.StabilizeIndex 'TableName', 'IndexName', ‘FillFactor’
It will update an index in a table with fill factor.

Note: This is not used in system database.

/* To Execute the sp in various options
--StabilizeIndex
--StabilizeIndex 'TableName'
--StabilizeIndex 'TableName', 'IndexName'
--StabilizeIndex 'TableName', 'IndexName', 'FillFactor'
*/
if exists (select * from sysobjects where id = object_id(N'[dbo].[StabilizeIndex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[StabilizeIndex]
GO

Create Proc StabilizeIndex
(
@TableName Varchar(100) = '',
@IndexName Varchar(100) = '',
@FillFactor Varchar(3) = ''
)
As

Declare @TableID Int, @ObjectName Varchar(100), @IndName Varchar(100), @SQL Varchar(8000)

SET NOCOUNT ON

IF Len(@TableName) = 0
Begin
DECLARE table_cursor CURSOR FOR
SELECT id, name
FROM sysobjects
WHERE xtype = 'U'
ORDER BY name
End
Else
Begin
DECLARE table_cursor CURSOR FOR
SELECT id, name
FROM sysobjects
WHERE xtype = 'U' and id = object_id(@TableName)
ORDER BY name
End

OPEN table_cursor

FETCH NEXT FROM table_cursor
INTO @TableID, @ObjectName

WHILE @@FETCH_STATUS = 0
BEGIN

IF Len(@IndexName) = 0
Begin
DECLARE index_cursor CURSOR FOR
SELECT name
FROM sysindexes
WHERE id = @TableID and indid > 0
End
Else
Begin
DECLARE index_cursor CURSOR FOR
SELECT name
FROM sysindexes
WHERE id = @TableID and name = @IndexName and indid > 0
End
OPEN index_cursor
FETCH NEXT FROM index_cursor INTO @IndName

WHILE @@FETCH_STATUS = 0
BEGIN
IF Len(@FillFactor) = 0
Begin
Set @SQL = 'DBCC DBREINDEX (' + @ObjectName + ',' + @IndName + ')'
End
Else
Begin
Set @SQL = 'DBCC DBREINDEX (' + @ObjectName + ',' + @IndName + ',' + @FillFactor + ')'
End

Exec (@SQL)

FETCH NEXT FROM index_cursor INTO @IndName

END

CLOSE index_cursor
DEALLOCATE index_cursor

FETCH NEXT FROM table_cursor
INTO @TableID, @ObjectName
END

CLOSE table_cursor
DEALLOCATE table_cursor

SET NOCOUNT OFF