Reindex Procedure with a Twist (or two)

/*
NOTE: A colleague recently asked me to implement reindex history in this process.  In working on that, I discovered that the procedure was NOT reindexing the way I thought it was.  The procedure was designed with the assumption that rebuilding a clustered index would automatically reindex all non-clustered indexes.  I have 
learned that this is only true for NON-UNIQUE clustered indexes.  Because of this, the procedure has been significantly modified.  If you have downloaded and implemented this procedure, please stop using it and download the new version.  The new version adds a history table, and a history load procedure.  Eventually, I will incorporate the history load logic into the reindex procedure.  Also, we are 
not currently using the fill factor logic in my shop, and I have other fires to fight, so the fill factor logic is untested in this version.  If you are, or want to use it, please test it first! 

Steve
*/
-- DBA_dbReindex (2k)


USE DBA
GO


--  Verify existence of DBA_dbReindex_showcontig table.  If it does not exist, create it.
if exists (select * from sysobjects where id = object_id(N'[dbo].[DBA_dbReindex_showcontig]') and 
OBJECTPROPERTY(id, N'IsTable') = 1)
print 'DBA_dbReindex_showcontig table exists.  it will not be created.'
else
begin
print 'creating DBA_dbReindex_showcontig_table.'
CREATE TABLE [DBA_dbReindex_showcontig] (
[DBName] [char] (128) NULL ,
[ObjectName] [char] (128) NULL ,
[ObjectId] [int] NULL ,
[IndexName] [char] (128) NULL ,
[IndexId] [int] NULL ,
[Lvl] [int] NULL ,
[CountPages] [int] NULL ,
[CountRows] [int] NULL ,
[MinRecSize] [int] NULL ,
[MaxRecSize] [int] NULL ,
[AvgRecSize] [int] NULL ,
[ForRecCount] [int] NULL ,
[Extents] [int] NULL ,
[ExtentSwitches] [int] NULL ,
[AvgFreeBytes] [int] NULL ,
[AvgPageDensity] [int] NULL ,
[ScanDensity] [decimal](18, 0) NULL ,
[BestCount] [int] NULL ,
[ActualCount] [int] NULL ,
[LogicalFrag] [decimal](18, 0) NULL ,
[ExtentFrag] [decimal](18, 0) NULL 
) ON [PRIMARY]
end
GO




-- Verify existence of DBA_Index_Stats table.  If it does not exist, create it.
if exists (select * from sysobjects where id = object_id(N'[dbo].[DBA_Index_Stats]') and 
OBJECTPROPERTY(id, N'IsTable') = 1)
print 'DBA_Index_Stats table exists.  it will not be created.'
else
begin
print 'creating DBA_Index_Stats table.'
CREATE TABLE [DBA_Index_Stats] (
[DbName] [varchar] (128) NULL ,
[TableName] [varchar] (128) NULL ,
[OwnerName] [varchar] (128) NULL ,
[IndexName] [varchar] (128) NULL ,
[ClusteredYN] [tinyint] NULL ,
[FillFactor] [tinyint] NULL ,
[LogicalFrag] [decimal](18, 0) NULL ,
[ScanDensity] [numeric](5, 0) NULL ,
[Rows] [int] NULL ,
[Pages] [int] NULL ,
[Reindex_Factor] [int] NOT NULL CONSTRAINT [DF__DBA_Index___Reind__6E01572D] DEFAULT (0),
[Process_Flag] [tinyint] NULL ,
[FillFactor_upd_flag] [tinyint] NULL ,
[Process_Date] [datetime] NULL 
) ON [PRIMARY]
end
GO


-- Verify existence of DBA_Index_History table.  If it does not exist, create it.
if exists (select * from sysobjects where id = object_id(N'[dbo].[DBA_Index_History]') and 
OBJECTPROPERTY(id, N'IsTable') = 1)
print 'DBA_Index_History table exists.  it will not be created.'
else
begin
print 'creating DBA_Index_History table.'
CREATE TABLE [DBA_Index_History] (
[DbName] [varchar] (128) NULL ,
[TableName] [varchar] (128) NULL ,
[OwnerName] [varchar] (128) NULL ,
[IndexName] [varchar] (128) NULL ,
[ClusteredYN] [tinyint] NULL ,
[FillFactor] [tinyint] NULL ,
[LogicalFrag] [decimal](18, 0) NULL ,
[ScanDensity] [numeric](5, 0) NULL ,
[Rows] [int] NULL ,
[Pages] [int] NULL ,
[Reindex_Factor] [int] NULL ,
[Process_Flag] [tinyint] NULL ,
[FillFactor_upd_flag] [tinyint] NULL ,
[Process_Date] [datetime] NULL ,
[Run_Date] [datetime] DEFAULT getdate()
) ON [PRIMARY]
end
GO



-- DBA_dbreindex_history_load

USE DBA

if exists (select * from sysobjects where id = object_id(N'[dbo].[DBA_dbReindex_history_load]') and 
OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
print 'dropping existing DBA_dbReindex_history_load procedure.'
drop procedure [dbo].[DBA_dbReindex_history_load]
end
else
print 'DBA_dbReindex_history_load does not currently exist.'

print 'creating DBA_dbReindex_history_load procedure.'
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  PROCEDURE  DBA_dbreindex_history_load

(@dbName sysname = NULL, 
@retention int = 1,
@retention_unit varchar(10) = 'month')

AS                        
SET NOCOUNT ON

/*************************************************************/                                                                                                                                                      
--                                                   
-- Module Name: DBA_dbreindex_history_load   
--                                                
-- Description: 
--   3 input parm(s). 
-- @dbName sysname : Name of the database that reindex data will be archived for.                 
--     @retention int : history will be held for this number of retention units.
--     @retention_unit varchar(10) : units for @retention (for the DATEADD function).
--Valid @retention_units are day, week, month, quarter, year.
--
-- Procedure description.
--This procedure migrates DBA_index_stats data to the DBA_index_history table, and deletes
-- the old history from the table.  Retention defaults to one month.
                                                  
-- Written By: Steve Phelps            
--                                                  
-- Date: June 02, 2005         
--                                                   
-- Modified :                                       
-- Date: 
-- Change:                                           
--                                                  
-- USAGE:                                            
--                                                  
--   exec DBA_dbreindex_history_load @databasename, @retention, @retention_unit     
--                            
/*************************************************************/   

PRINT 'Version: 2005/06/08 14:30:00
' 

-- the following declare must be removed. its here for testing.
-- DECLARE
--@dbName sysname,
-- @retention int,
-- @retention_unit varchar(10),  -- day, week, month, quarter, year
-- SELECT 
--@dbName = 'RSVP_Production',
--@retention = 1, 
--@retention_unit = 'month'
-- end of test logic


DECLARE
@MinDate datetime

IF @dbName is NULL
BEGIN
RAISERROR ('Database name must be entered.', 16, 1)
RETURN -1
END

IF @retention_unit NOT IN ('day', 'week', 'month', 'quarter', 'year')
BEGIN
RAISERROR ('Valid @retention_units are day, week, month, quarter, year.', 16, 1)
RETURN -1
END

SELECT @retention = @retention * -1

SELECT @MinDate = 
CASE
WHEN @retention_unit = 'day' THEN DATEADD(day, @retention, getdate())
WHEN @retention_unit = 'week' THEN DATEADD(week, @retention, getdate())
WHEN @retention_unit = 'month' THEN DATEADD(month, @retention, getdate())
WHEN @retention_unit = 'quarter' THEN DATEADD(quarter, @retention, getdate())
WHEN @retention_unit = 'year' THEN DATEADD(year, @retention, getdate())
END 

-- delete historical data which is older than the retention period.
DELETE FROM DBA..DBA_Index_History
WHERE Run_Date < @MinDate
AND DbName = @dbName

-- Load the current run's data into the history table.
INSERT INTO [DBA].[dbo].[DBA_Index_History]
([DbName], [TableName], [OwnerName], [IndexName], 
[ClusteredYN], [FillFactor], [LogicalFrag], [ScanDensity], 
[Rows], [Pages], [Reindex_Factor], [Process_Flag], 
[FillFactor_upd_flag], [Process_Date])
SELECT 
[DbName], [TableName], [OwnerName], [IndexName], 
[ClusteredYN], [FillFactor], [LogicalFrag], [ScanDensity], 
[Rows], [Pages], [Reindex_Factor], [Process_Flag], 
[FillFactor_upd_flag], [Process_Date] 
FROM [DBA].[dbo].[DBA_Index_Stats]
WHERE DbName = @dbName


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





-- Verify existence of DBA_dbReindex procedure.  If it does exist, drop it, so that it can be re-created.
if exists (select * from sysobjects where id = object_id(N'[dbo].[DBA_dbReindex]') and 
OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
print 'dropping existing DBA_dbReindex procedure.'
drop procedure [dbo].[DBA_dbReindex]
end
else
print 'DBA_dbReindex does not currently exist.'

print 'creating DBA_dbReindex procedure.'
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE dbo.DBA_dbReindex
@dbNamevarchar(128),
@limitnumeric,
@limit_type             varchar(10),
@ScanDensity_Thresholdint,
@FillFactor_Age_Limitint = 0,
@Test_Run_Flagint

/********************************************************************/                                                                                                                                                      
--                                                           
--  Module Name: DBA_dbReindex                                
--                                                           
--  Description: Receives 6 parms.                          
--    @dbName  : database for which reindex is run           
--    @limit : max number or percent of indexes to rebuild
--    @limit_type : allows for setting @limit as either 'percent' or
--      'count'.  if the parameter is populated with anything
--      other than percent (or %), count will be used.  please populate
--      with one of these two values, to ease understandability.  If
--      you wish to reindex the entire database, set @limit = 100 and 
--      @limit_type = 'percent'.           
--    @ScanDensity_Threshold: any index with scan density          
--      lower than this value will be candidate for rebuild
--@FillFactor_Age_Limit: If an index has been rebuilt within the
--number of days specified by this parameter, the fillfactor will
--be reduced by 5, to a minimum of 70.  If the limit of 70 has been
--reached, a pop-up message will be sent to the on-call dba.  With 
--the default value of 0, no action is taken.   
--@Test_Run_Flag: This flag controls procedure functionality.
--values:
--0: production, no limitations on functionality
--1: test, execution of dbcc dbreindex is inhibited. 
--dbcc dbreindex command is printed to the screen.
--update statistics is inhibited.
--2: all restrictions included in "1." plus
--DBA.dbo.DBA_dbReindex_showcontig is not updated.
--                                                            
--    This procedure performs a reindex on a given            
--    database.  It runs DBCC ShowContig to retrieve         
--    information on the indexes so that a decision       
--    can be made which indexes need to be rebuilt and also
--pulls information from sysobjects and sysindexes.          
--    It sorts the indexes from worst to best, and          
--    then rebuilds the '@limit' worst indexes.  
--
--  Written By:  Steve Phelps              
--                                                           
--  Date: December 27, 2002                                    
--                                                            
--  Modified : modified procedure to only explicitly reindex the clustered index if one exists.
--Also changed procedure to use pages rather than rows in determining which indexes
--to rebuild.                                               
--  Date: 6/12/2003                                                    
--                                                            
--  Modified : add LogicalFrag to DBA_Index_Stats.  Set reindex_factor to not null and provide
--          default value of 0.  Don't calc reindex_factor if scandensity < @ScanDensity_Threshold.
--          Add FillFactor_age_limit functionality. Removed FillFactor_Override.
--  Date: 2/05/2004                                                    
--                                                            
--  Modified : added logic to check for existence of table before executing DBCC DBREINDEX.  In
--particular, Goldmine creates and deletes tables dynamically.  At least once, a table was
--deleted after the showcontig, and before the reindex, causing the job to fail.                                              
--  Date:3/4/2004                                                    
--                                                            
--  Modified :  fixed table existence logic.
--  Date: 1/12/2005 sbp                                                    
--
--  Modified : fixed test flag = 1, added "print version:"
--  Date:3/9/2005    
--
--  Modified : changed logic to reindex only those indexes identified by reindex_factor.  Clustered
--indexes are no longer treated differently.  removed update stats.  also added the DBA_index_history
--  table and wrote DBA_dbreindex_history_load, which should be run immediately after this procedure.
--  Eventually, DBA_dbreindex_history_load should be incorporated into this procedure.
--  Date:6/8/2005 sbp   
--                                                            
--  USAGE:                                                    
--                                                            
--    -- Before implementing this procedure, be sure that     
--    -- you understand its usage, and are able to make      
--    -- an educated decision as to the values of the         
--    -- parameters, and the scheduling of the task.  This   
--    -- procedure can have a tremendous impact on the       
--    -- performance of a database, either positive or negative.  
--    --
--    --    You can determine the current fill factor for a particular 
--    --    index by running the following query:
--    --    USE pubs
--    --    SELECT INDEXPROPERTY(OBJECT_ID('authors'),    <=  table name
--    --'UPKCL_auidind',         <=  index name
--    --   'IndexFillFactor')       <=  parameter specifying fill factor  
--
--Before implementing this procedure, you should verify that the required tables
--have been created in the DBA database.  This can be done by executing the script
--DBA_dbReindex_create_tables.sql .  This procedure should also be placed in and
--executed from the DBA database.  IF the original script is used to create this
--procedure, this is all taken care of within the script!
--                                                                                                       
-- DECLARE
-- @dbNamevarchar(128),
-- @limitnumeric,
-- @limit_typevarchar(10),
-- @ScanDensity_Thresholdint,
-- @FillFactor_Age_Limitint,
-- @Test_Run_Flagint
-- 
-- select @dbName = 'PUBS'                                
-- select @limit =  50    
-- select @limit_type = 'percent' -- or '%' or 'count'                             
-- select @ScanDensity_Threshold = 80
-- select @FillFactor_Age_Limit = 3                         
-- select @Test_Run_Flag = 0 -- 0 or 1 or 2
-- 
-- exec DBA_dbReindex @dbName, @limit, @limit_type,                 
-- @ScanDensity_Threshold, @FillFactor_Age_Limit, @Test_Run_Flag 
--
-- exec DBA_dbreindex_history_load @dbName, 1, 'month'          
--
/********************************************************************/   


AS
SET NOCOUNT ON
PRINT 'Version: 2005/06/08 14:30:00
'

-- -- The following Declarations are for test purposes only.
-- 
-- DECLARE
-- @dbName varchar(128),
-- @limit numeric,
-- @limit_type varchar(10),
-- @ScanDensity_Threshold int,
-- @FillFactor_Age_Limit int,
-- @Test_Run_Flag int
-- 
-- select @dbName = 'RSVP_Billing'                                
-- select @limit =  100    
-- select @limit_type = 'percent' -- or '%' or 'count'                             
-- select @ScanDensity_Threshold = 95
-- select @FillFactor_Age_Limit = null                         
-- select @Test_Run_Flag = 1 -- 0 or 1 or 2
-- 
-- -- end test declarations

DECLARE
@TableNameVARCHAR(128),
@OwnerNameVARCHAR(128),
@IndexNameVARCHAR(128),
@FF_IndexNameVARCHAR(128),
@FillFactorINT,
@CounterINT,
@Inner_CounterINT,
@Inner_LimitINT,
@ClusteredYNINT,
@RowsINT,
@PagesINT,
@ExitTINYINT,
@commandNVARCHAR(2000),
@command2  NVARCHAR(2000),
@parmlist NVARCHAR(100),
@flag TINYINT,
@job_statusTINYINT,
@Todays_DateDATETIME,
@ScanDensity tinyint,
@Reindex_Factor int,
@txt varchar(500),
@string nvarchar(500)

-- create a temporary table to hold data for indexes for which the fillfactor should be re-calculated.
CREATE TABLE #FillFactor_Table
(
#TblName varchar(128),
#OwnerName varchar(128),
#IndexName varchar(128),
#FillFactor int,
#Process_Flag tinyint,
#Process_Date datetime,
#FF_Flag tinyint default 0
)

-- create a temporary table to hold the results of DBCC Showcontig
CREATE TABLE #Showcontig
(
[#ObjectName] [char] (128),
[#ObjectId] [int],
[#IndexName] [char] (128),
[#IndexId] [int],
[#Lvl] [int],
[#CountPages] [int],
[#CountRows] [int],
[#MinRecSize] [int],
[#MaxRecSize] [int],
[#AvgRecSize] [int],
[#ForRecCount] [int],
[#Extents] [int] NULL,
[#ExtentSwitches] [int],
[#AvgFreeBytes] [int],
[#AvgPageDensity] [int],
[#ScanDensity] [decimal](18, 0),
[#BestCount] [int],
[#ActualCount] [int],
[#LogicalFrag] [decimal](18, 0),
[#ExtentFrag] [decimal](18, 0)
)

set @Todays_Date = getdate()

-- delete records from table DBA.dbo.DBA_Index_Stats for current db after saving fill_factor and processing information.
insert #FillFactor_Table
select 
TableName,
OwnerName,
IndexName,
[FillFactor],
Process_Flag,
Process_Date,
0
from DBA_Index_Stats
wheredbName = @dbName
and Process_Flag in (1,2)

Delete 
from DBA.dbo.DBA_Index_Stats
where dbName = @dbName

--  delete records from DBA_dbReindex_showcontig for current db.
if @Test_Run_Flag <> 2
begin
Delete 
from DBA.dbo.DBA_dbReindex_showcontig
where dbName = @dbName

-- Build the command to execute a DBCC SHOWCONTIG on the specified database.  The
-- results will be stored in the DBA_dbReindex_showcontig table.
select @command = 
'USE ' + @dbName + ' 
 DBCC SHOWCONTIG 
WITHFAST, 
TABLERESULTS, 
ALL_INDEXES, 
NO_INFOMSGS'

-- print @command
INSERT INTO #showcontig
 EXEC (@command)
set @command = null

INSERT INTO DBA.dbo.DBA_dbReindex_showcontig
select
@dbName,
#ObjectName,
#ObjectId,
#IndexName,
#IndexId,
#Lvl,
#CountPages,
#CountRows,
#MinRecSize,
#MaxRecSize,
#AvgRecSize,
#ForRecCount,
#Extents,
#ExtentSwitches,
#AvgFreeBytes,
#AvgPageDensity,
#ScanDensity,
#BestCount,
#ActualCount,
#LogicalFrag,
#ExtentFrag
from #showcontig
end -- if @Test_Run_Flag <> 2

--  Build the command to populate the DBA_Index_Stats table.  This is the table
--  that is used to determine which indexes should be rebuilt, and in what order.
--Note:  
--indid = 0 = Heap
--indid = 1 = Clustered index 
--indid = 2 - 249 = nonclustered index 
--indid = 255 is NOT an index, it is an entry for tables specifying that the table has text
--or image column(s).
--This procedure will ignore indid = 0 or indid = 255!
select @command = 
'SELECT ' + char(39) + @dbName + char(39) + ',
SO.[name],
SU.[name], 
SI.[name], 
CASE WHEN SI.indid = 1 THEN 1 ELSE 0 END,
MAX(SI.OrigFillFactor),
SC.[LogicalFrag], 
SC.[ScanDensity],
SI.[RowCnt],
SC.[CountPages], 
0,
0,
Null,
Null
FROM ' + @dbName + '.dbo.sysindexes SI
LEFT JOIN ' + @dbName + '.dbo.sysobjects SO ON SI.[id] = SO.[id]
LEFT JOIN DBA.dbo.DBA_dbreindex_showcontig SC ON SI.[id] = SC.[ObjectId]
AND SI.[IndId] = SC.[IndexId]
AND SC.dbName = ' + char(39) + @dbName + char(39) + '
LEFT JOIN ' + @dbName + '.dbo.sysusers SU ON SO.uid = SU.uid
WHERE SO.[xtype] = ''U'' 
AND SO.[status] > 0
AND SI.indid <> 0 
AND SI.indid <> 255 
AND SI.[dpages] > 0
GROUP BY SO.[name], SU.[name], SI.[name], SI.indid, SC.[LogicalFrag], SC.[ScanDensity], SI.[RowCnt], SC.[CountPages]'

-- print @command
insert DBA.dbo.DBA_Index_Stats
 EXEC (@command)
set @command = null

-- If the fillfactor age limit parameter is populated, calculate a new fill factor for indexes which have been
-- rebuilt within that time limit, and are candidates for rebuilding.  

/*------------------------------------------------------------------------------------------------------------
FLAW!!!  age limit can only work for the previous run!  NOT # of days, because of the way the #FillFactor_Table
is being populated.  

From an e-mail to Lezza -

Modification to fix the fillfactor routine would take care of the history as well.  But its going to require some 
thought to decide on the best way to handle it.  In the mean time, I COULD migrate the DBA_index_stats table to an 
archive table that would give you previous run information.  Could either overwrite it with each run, or put a 
"Run_Date" with current date default and then just do an insert.  Table would grow quickly though, so would need to 
have a process to remove old records.  Would be pretty simple to set up.  Actually, a history table with "Run_Date" 
would be useful in fixing the fill_factor logic as well.  The logic itself isn't a priority as we aren't currently 
using it.

This has been implemented, but the fill factor logic has not been tested!!!
------------------------------------------------------------------------------------------------------------*/
-- Note that we are looking at the DBA_Index_History table for previous run information.  NOT TESTED!!!
if @FillFactor_Age_Limit > 0
update #FillFactor_Table
set #FillFactor = 
case
when I.[FillFactor] = 0 and I.ScanDensity < @ScanDensity_Threshold then 95
when I.[FillFactor] between 70 and 75 and I.ScanDensity < @ScanDensity_Threshold then 70
when I.[FillFactor] between 75 and 99 and I.ScanDensity < @ScanDensity_Threshold then I.[FillFactor] - 5
else I.[FillFactor] 
end
from DBA.dbo.DBA_Index_History I
join #FillFactor_Table F
on  I.dbName = @dbName
and I.TableName = F.#tblname
and I.OwnerName = F.#OwnerName
and I.IndexName = F.#indexname
and F.#Process_Flag = 1
andDateDiff(DD, Process_Date, @Todays_Date) <= @FillFactor_Age_Limit

-- The following code calculates a "priority" for reindexing based on Scan Density and the 
-- number of pages in the table.  Tables with more pages receive a higher priority, and 
-- indexes with a lower Scan Density receive a higher priority.  An index with a high Scan
-- density may receive a higher priority than one with an extremely low Scan Density if 
-- it has a much greater number of pages.  This is desirable, as a low Scan Density has 
-- little meaning in a table with few pages.  Also, a Reindex_Factor will not be calculated
-- if the ScanDensity = 100 or is greater than the threshold.
update DBA.dbo.DBA_Index_Stats
set Reindex_Factor = (((100 - ScanDensity) / 100) * [Pages])
from DBA.dbo.DBA_Index_Stats
where ScanDensity < @ScanDensity_Threshold
and dbName = @dbName

-- Get the number of indexes that are candidates for rebuilding.
select @Rows = count(*)
from DBA.dbo.DBA_Index_Stats
where ScanDensity <= @ScanDensity_Threshold
and dbName = @dbName

-- If @limit_type is percent, calculate @limit percent of @Rows.  If @limit_type is any
-- value other than '%' or 'percent', 'count' is assumed, and @limit is used as a literal.
if @limit_type = '%' or 
@limit_type = 'percent'
begin 
select @limit = (@limit * .01) * @Rows
end

-- It is possible for @limit to be greater than the number of indexes that are candidates.  
-- If this occurs, reset @limit to be equal to @Rows.
if @limit > @Rows
set @limit = @Rows

set @Exit = 0
set @Counter = 1
While @Counter <= @limit and @Exit = 0
 Begin
-- Retrieve data from DBA_Index_Stats to build the DBCC dbReindex command.  The table is read in
-- Reindex_Factor descending order.  Once a candidate has been selected, it is reindexed with
-- the DBCC DBREINDEX command, then Process_Flag is set to '1', eliminating it from the pool.  

-- Initiate processing for the current index.
Select top 1
@TableName = TableName,
@OwnerName = OwnerName,
@IndexName = IndexName,
@FillFactor = [FillFactor]
from DBA.dbo.DBA_Index_Stats
where Process_Flag = 0
and dbName = @dbName
Order by
Reindex_Factor desc,
ScanDensity Asc,
Pages Desc,
LogicalFrag desc

-- Check if a record exists in #FillFactor_table.  If so, set the FillFactor for the 
-- DBCC DBReindex command = the value from #FillFactor_table.
If exists(select 1 from #FillFactor_Table where #Tblname = @TableName and #IndexName = @IndexName)
begin
select @FillFactor = #FillFactor
from #FillFactor_Table
where #Tblname = @TableName 
and #IndexName = @IndexName
update #FillFactor_Table
set #FF_Flag = 1
where #Tblname = @TableName
and #IndexName = @IndexName
end

Process_Index:

-- Build the DBCC dbReindex command
Set @command = 
  'DBCC DBREINDEX ' + '(' + CHAR(39) + @dbName + '.' + @OwnerName + '.' + @TableName + CHAR(39)
+ CHAR(44) + SPACE(1) + CHAR(39) + @IndexName + CHAR(39) + CHAR(44) 
+ SPACE(1) + CONVERT(VARCHAR(3), RTRIM(@FillFactor)) + ')'

print @command

set @string = @dbName + '.' + @OwnerName + '.' + @TableName

-- Verify that the table still exists before executing the reindex.  Goldmine, in particular, has a nasty habit
-- of deleting tables when it sees fit.  This has caused this procedure to generate errors, leading to this solution.
set @command2 = N'select @flag = 1 from ' + @DBName + N'.dbo.sysobjects where id = object_id(''' + @string + N''') and xtype = ''U'''
set @parmlist = '@flag as tinyint OUTPUT'
EXEC sp_executesql @command2, @parmlist, @flag OUTPUT
if @Test_Run_Flag = 0 
BEGIN
if @flag = 1
BEGIN
 exec sp_executesql @command
END -- if @flag = 1
END -- if @Test_Run_Flag = 0

-- Set Process_Flag in DBA_Index_Stats for the index we just rebuilt to 1, eliminating it from the potential
-- indexes to rebuild.  
update DBA.dbo.DBA_Index_Stats
set 
Process_Flag = 1,
Process_Date = @Todays_Date
where dbName = @dbName
and TableName = @TableName
andIndexName = @IndexName

-- increment counter
set @Counter = @Counter + 1

-- if all indexes that are candidates have been rebuilt, get out of the loop.
if not exists (select 1 from DBA.dbo.DBA_Index_Stats 
where dbName = @dbName and Process_Flag = 0 and ScanDensity < @ScanDensity_Threshold)
set @Exit = 1 
 end -- While @Counter

-- Fill Factor notification here...
--/*

-- update DBA_Index_Stats with new fillfactor, and set FillFactor_upd_flag to show that the fillfactor was updated.
update DBA.dbo.DBA_Index_Stats
set FillFactor_upd_flag = 1,
[FillFactor] = F.#FillFactor
from DBA.dbo.DBA_Index_Stats I
join #FillFactor_Table F
on I.TableName = F.#tblname
and I.IndexName = F.#IndexName
and I.[FillFactor] <> F.#FillFactor
and I.dbName = @dbName
and I.Process_Flag = 1


-- send an alert to the on call dba when any fillfactor reaches 70.  this alert should only be sent when
-- the fillfactor CHANGES to 70, not if it was 70 before running DBA_dbReindex.
-- Change the following code to check against DBA_index_history, checking the previous run for fillfactor > 70.  This will 
-- ensure that the alert will only be fired when the fillfactor actually CHANGES to 70.
-- if exists(select 1 from DBA.dbo.DBA_Index_Stats where [FillFactor] = 70 and FillFactor_upd_flag = 1 and dbName = @dbName)
-- exec a_AlertCall 15

cleanup:

drop table #FillFactor_Table
drop table #showcontig

-- test stuff 
/*
select * from #fillfactor_table
select * from DBA_Index_Stats order by dbName, Reindex_Factor desc
select * from DBA_dbReindex_showcontig
truncate table DBA_index_stats

select count(*) from DBA.dbo.DBA_Index_Stats

select count(*) from #fillfactor_table
*/-- end test stuff

job_abend:
if @@error <> 0
return @@error

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO