Reindex Procedure with a Twist (or two) the SQL

USE [master]
GO
/****** Object:  Database [DBA]    Script Date: 01/29/2008 10:36:04 ******/CREATE DATABASE [DBA] 
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'DBA', @new_cmptlevel=110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [DBA].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [DBA] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [DBA] SET ANSI_NULLS OFF
GO
ALTER DATABASE [DBA] SET ANSI_PADDING OFF
GO
ALTER DATABASE [DBA] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [DBA] SET ARITHABORT OFF
GO
ALTER DATABASE [DBA] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [DBA] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [DBA] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [DBA] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [DBA] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [DBA] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [DBA] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [DBA] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [DBA] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [DBA] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [DBA] SET  DISABLE_BROKER
GO
ALTER DATABASE [DBA] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [DBA] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [DBA] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [DBA] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [DBA] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [DBA] SET  READ_WRITE
GO
ALTER DATABASE [DBA] SET RECOVERY FULL
GO
ALTER DATABASE [DBA] SET  MULTI_USER
GO
ALTER DATABASE [DBA] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [DBA] SET DB_CHAINING OFF
GO
USE [DBA]
GO
/****** Object:  Table [dbo].[DBA_dbReindex_showcontig]    Script Date: 01/29/2008 10:36:04 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[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]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[DBA_Index_Stats]    Script Date: 01/29/2008 10:36:04 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[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]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[DBA_Index_History]    Script Date: 01/29/2008 10:36:04 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[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] NULL DEFAULT (getdate())
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [DBA]
GO

USE [DBA]
GO

/****** Object:  Table [dbo].[DBA_LOB_Indexes]    Script Date: 12/5/2013 7:26:01 AM ******/SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DBA_LOB_Indexes](
[DBName] [nvarchar](130) NULL,
[FileGroupName] [nvarchar](128) NULL,
[TableName] [nvarchar](128) NULL,
[IndexName] [sysname] NULL,
[LOBUsedPages] [bigint] NOT NULL,
[LOBTotalPages] [bigint] NOT NULL,
[LOBDataSizeMB] [bigint] NULL,
[AllocUnitType] [nvarchar](60) NULL
) ON [PRIMARY]

GO





USE [DBA]
GO

/****** Object:  StoredProcedure [dbo].[DBA_dbReindex]    Script Date: 5/12/2014 1:35:24 PM ******/SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO








CREATE PROCEDURE [dbo].[DBA_dbReindex]
@dbNamevarchar(128),
@limitnumeric,
@limit_type             varchar(10),
@ScanDensity_Thresholdint,
@FillFactor_Age_Limitint = 0,
@Test_Run_Flagint,
@offlinebit = 0,
@MinimalLoggingbit = 1,
@MinimumPagesint = 500,
@ExlusionListvarchar(2000) = null

/********************************************************************/                                                                                                                                                      
--                                                           
--  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 Alter Index is inhibited. 
--Alter Index command is printed to the screen.
--update statistics is inhibited.
--2: all restrictions included in "1." plus
--DBA.dbo.DBA_dbReindex_showcontig and [dbo].[DBA_LOB_Indexes] 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   
--  
--Modifications to this code After 6/8/2005 were made by Keith Hays with many thanks to Stepen Phelps
--                                                            
--  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
SET DEADLOCK_PRIORITY HIGH 
PRINT 'DBA Database Version: 2013/04/13 15: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
-- @offlinebit = 0,
--@MinimalLoggingbit = 1
-- -- 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),
@@MESSAGE varchar(2000),
@@MESSAGE2 VARCHAR(2000),
@@MESSAGE3 VARCHAR(2000),
@SCANMODE NVARCHAR(20),
@LOB BIT,
@RecoveryModelStart nvarchar(60),
@RecoveryModelFinish nvarchar(60),
@c_pos int,
@n_pos int,
@l_pos int

DECLARE @ExlusionsTable Table (DBName varchar(128))



SET @SCANMODE = 'LIMITED' 
SET @dbName = '['+ REPLACE(REPLACE(@DBNAME,'[',''),']','') +']'


IF (select count(*) FROM sys.databases where '['+name+']' = @dbName AND source_database_id IS NOT NULL) > 0
BEGIN
GOTO job_abend
END -- EXIT IF DATABASE IS SNAPSHOT

IF  NOT (@ExlusionList is null OR LTRIM(RTRIM(@ExlusionList)) = '')
BEGIN
IF CHARINDEX(',',@ExlusionList) = 0 or CHARINDEX(',',@ExlusionList) is null
BEGIN
insert into @ExlusionsTable (DBName) values ('['+ REPLACE(REPLACE(REPLACE(UPPER(@ExlusionList),' ',''),'[',''),']','') +']')
END
ELSE
BEGIN
set @c_pos = 0;
set @n_pos = CHARINDEX(',',@ExlusionList,@c_pos)

while @n_pos > 0
BEGIN
insert into @ExlusionsTable (DBName) values (SUBSTRING(@ExlusionList,@c_pos+1,@n_pos - @c_pos-1))
set @c_pos = @n_pos
set @l_pos = @n_pos
set @n_pos = CHARINDEX(',',@ExlusionList,@c_pos+1)
END

insert into @ExlusionsTable (DBName) values (SUBSTRING(@ExlusionList,@l_pos+1,4000))

UPDATE @ExlusionsTable
SET DBName = '['+ REPLACE(REPLACE(REPLACE(UPPER(DBName),' ', ''),'[',''),']','') +']'
END
IF (SELECT COUNT(*) FROM @ExlusionsTable WHERE DBNAME = @dbName) > 0
BEGIN
GOTO job_abend
END
END -- EXIT IF DATABASE IS EXCLUDED



IF @MinimalLogging = 1 and @Test_Run_Flag = 0
BEGIN
select @RecoveryModelStart = recovery_model_desc
FROM sys.databases where '['+name+']' = @dbName

if @RecoveryModelStart = 'FULL' and @dbName not in ('[Master]', '[Model]', '[MSDB]', '[TEMPDB]', '[SSISDB]')  
begin
SET @command = 'ALTER DATABASE ' + @DBNAME + ' SET RECOVERY BULK_LOGGED'
EXEC (@command)
SET @command = null
end
IF @RecoveryModelStart = 'BULK_LOGGED'
BEGIN
SET @RecoveryModelStart = 'FULL'
END
END

-- 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)
)

CREATE TABLE #indstats 
(
    indstatsid INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    database_id BIGINT,
    index_id BIGINT,
    Object_IDBigInt
)


if @Test_Run_Flag <> 2
BEGIN
select @command = 
'use ' +  @dbName + '; '+
'SELECT database_id,index_id,object_id
FROM sys.dm_db_index_physical_stats(DB_ID(),null,NULL,NULL,''LIMITED'') ps'


INSERT INTO #indstats
EXEC (@command)
set @command = null

Delete 
from [DBA].dbo.[DBA_LOB_Indexes]
where dbName = @dbName

select @command = 
'use ' +  @dbName + '; '+
'SELECT DISTINCT '''+  @dbname + ''' as DBName, FileGroupName = FILEGROUP_NAME(a.data_space_id)
,TableName = OBJECT_NAME(p.OBJECT_ID)
,IndexName = i.name
,LOBUsedPages = a.used_pages
,LOBTotalPages = a.total_pages
,LOBDataSizeMB = a.used_pages * 8/1024
,a.type_desc AS AllocUnitType


FROM sys.allocation_units a
INNER Join sys.partitions p
ON p.partition_id = a.container_id
And a.type = 2                                  --LOB data is stored in pages of type Text/Image
LEFT Outer Join sys.dm_db_index_usage_stats us
ON us.OBJECT_ID = p.OBJECT_ID
And us.index_id = p.index_id
And us.database_id = DB_ID()
LEFT Outer Join #indstats ps
ON us.index_id = ps.index_id
And us.database_id = ps.database_id
And us.OBJECT_ID = ps.OBJECT_ID
LEFT Outer Join sys.indexes i
ON i.OBJECT_ID = p.OBJECT_ID
And i.index_id = p.index_id
WHERE OBJECTPROPERTY(p.OBJECT_ID,''IsMSShipped'') = 0 and i.name is not null
  ORDER BY dbname, TableName ASC,a.type_desc'



  INSERT INTO [DBA].[dbo].DBA_LOB_INDEXES
   EXEC (@command)
  set @command = null
END  -- if @Test_Run_Flag <> 2 for LOB Information




set @Todays_Date = getdate()

-- Populate Fill Factor information for current db from history.


insert #FillFactor_Table
SELECT     TableName, OwnerName, IndexName, [FillFactor], Process_Flag, MAX(Process_Date) AS Process_Date, 0
FROM         DBA_Index_History
WHERE     dbName = @dbName and (Process_Flag in (1,2))
GROUP BY TableName, OwnerName, IndexName, [FillFactor], Process_Flag




--REBUILD DBA.dbo.DBA_dbReindex_showcontig from sys.dm_db_index_physical_stats
if @Test_Run_Flag <> 2
begin
--  delete records from DBA_dbReindex_showcontig for current db.
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 + '
SELECT OBJECT_NAME(p.object_id) AS ObjectName, p.object_id, isnull(i.name,'''') as IndexName, p.index_id as IndexID, 
p.index_level as Level,
p.page_count as Pages, p.record_count AS [Rows], p.min_record_size_in_bytes AS MiniumumRecordSize, p.max_record_size_in_bytes as MaximumRecordSize,
p.avg_record_size_in_bytes as AverageRecordSize, p.forwarded_record_count as ForwardedRecords,
null as Extents, null as ExtentSwitches, null as AverageFreeBytes, 
p.avg_page_space_used_in_percent as AveragePageDensity, (100-p.avg_fragmentation_in_percent) as ScanDensity,
1 as BestCount, 1 as ActualCount, p.avg_fragmentation_in_percent as LogicalFragmentation,
p.avg_fragmentation_in_percent AS ExtentFragmentation 
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, '+ CHAR(39)+ @SCANMODE + CHAR(39)+') AS p
INNER JOIN sys.sysdatabases AS d ON p.database_id = d.dbid
LEFT OUTER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id'

--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 REBUILD DBA.dbo.DBA_dbReindex_showcontig from sys.dm_db_index_physical_stats


if 1 = 1 --WHICH INDEXES TO REBUILD
BEGIN
--  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!
--Delete Index Statistics from Permanant Table before rebuilding
Delete 
from DBA.dbo.DBA_Index_Stats
where dbName = @dbName

select @command = 'use ' +  @dbName + '; '+ 
'SELECT ' + char(39) + @dbName + char(39) + ',
SO.[name],
--SU.[name],
OBJECT_SCHEMA_NAME(SO.ID), 
SI.[name], 
CASE WHEN SI.indid = 1 THEN 1 ELSE 0 END,
CASE WHEN MAX(SI.OrigFillFactor) = 0 THEN 100 ELSE MAX(SI.OrigFillFactor) END,
cast(avg(SC.[LogicalFrag]) as decimal(18,0)) as [LogicalFrag], 
cast(avg(SC.[ScanDensity]) as decimal(18,0)) as [ScanDensity],
SI.[RowCnt],
SUM(SC.[CountPages]) as [CountPages],
0,
0,
Null,
Null
FROM ' + @dbName + '.sys.sysindexes SI
LEFT JOIN ' + @dbName + '.sys.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 + '.sys.sysusers SU ON SO.uid = SU.uid
WHERE SO.[xtype] = ''U'' 
--AND SO.[status] > 0
AND SI.indid <> 0 
AND SI.indid <> 255
AND SC.[ScanDensity] < ' + CAST(@ScanDensity_Threshold AS VARCHAR(25)) + '
AND SI.[dpages] > 8 ' + ' 
         AND ' + char(39) + @dbName + char(39) + ' IN  (select + CHAR(91) + NAME + CHAR(93) from sys.databases where is_read_only = 0 and name not in (''tempdb''))
GROUP BY SO.[name], OBJECT_SCHEMA_NAME(SO.ID), SI.[name], SI.indid, SI.[RowCnt]
HAVING SUM(SC.[CountPages]) > ' + cast(@MinimumPages as varchar(25)) + ''
--CHECK FOR READ_ONLY ADDED 12/4/2007\
--CHECK FOR SCHEMA OWNER DIFFRERENT FROM SCHEMA 4-10-2013
--print @command
--goto job_abend
insert DBA.dbo.DBA_Index_Stats
 EXEC (@command)
set @command = null

END -- WHICH INDEXES TO REBUILD

  
if @FillFactor_Age_Limit > 0
-- 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.  
-- Note that we are looking at the DBA_Index_History table for previous run information.
BEGIN
update #FillFactor_Table
set #FillFactor = 
case
when I.[FillFactor]= 100 and I.ScanDensity < @ScanDensity_Threshold then 95
when I.[FillFactor] between 50 and 55 and I.ScanDensity < @ScanDensity_Threshold then 50
when I.[FillFactor] between 55 and 99 and I.ScanDensity < @ScanDensity_Threshold then I.[FillFactor] - 5
when I.[FillFactor] = 0 then 100
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 I.Process_Date = F.#Process_Date
and F.#Process_Flag = 1
andDateDiff(DD, Process_Date, @Todays_Date) <= @FillFactor_Age_Limit
END  --CHECK FILL FACTOR 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 -- Index Build While @Counter
Begin
-- Retrieve data from DBA_Index_Stats to build the ALTER INDEX command.  The table is read in
-- Reindex_Factor descending order.  Once a candidate has been selected, it is reindexed with
-- the ALTER INDEX 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
SELECT   TOP 1 
@TableName = TableName, 
@OwnerName = OwnerName, 
@IndexName = IndexName, 
@FillFactor = [FillFactor]
FROM    DBA.dbo.DBA_Index_Stats
where Process_Flag = 0 and dbName = @dbName
GROUP BY TableName, OwnerName, IndexName, [FillFactor]
ORDER BY SUM(Reindex_Factor) 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
--CHECK IF INDEX CONTAINS LOB
SET @LOB = 0
If exists(select 1 from DBA.[dbo].[DBA_LOB_Indexes] where [TableName] = @TableName and IndexName = @IndexName AND [DbName] = @dbName)
BEGIN
SET @LOB = 1
END
Process_Index:

-- Build the Alter Index command


IF LEFT(CAST(SERVERPROPERTY ('edition') AS VARCHAR(1)),1) = 'E' and @LOB = 0 and @offline = 0
BEGIN
Set @command = 
'Alter Index ' + @IndexName 
+ ' on ' + @dbName + '.[' + @OwnerName + '].[' + @TableName + ']'
+ ' REBUILD WITH ' 
+ '(' + 'FILLFACTOR=' + RTRIM(@FillFactor) + CHAR(44) + SPACE(1)
+ 'ONLINE=ON' + CHAR(44) + SPACE(1)
+ 'MAXDOP=2' + ')'
END
ELSE IF LEFT(CAST(SERVERPROPERTY ('edition') AS VARCHAR(1)),1) = 'E' and @LOB = 1 and @offline = 1
BEGIN
Set @command = 
'Alter Index ' + @IndexName 
+ ' on ' + @dbName + '.[' + @OwnerName + '].[' + @TableName + ']'
+ ' REBUILD WITH ' 
+ '(' + 'FILLFACTOR=' + RTRIM(@FillFactor) + CHAR(44) + SPACE(1)
+ 'ONLINE=OFF' + CHAR(44) + SPACE(1)
+ 'MAXDOP=2' + ')'
END
ELSE IF LEFT(CAST(SERVERPROPERTY ('edition') AS VARCHAR(1)),1) = 'E' and @LOB = 0 and @offline = 1
BEGIN
Set @command = 
'Alter Index ' + @IndexName 
+ ' on ' + @dbName + '.[' + @OwnerName + '].[' + @TableName + ']'
+ ' REBUILD WITH ' 
+ '(' + 'FILLFACTOR=' + RTRIM(@FillFactor) + CHAR(44) + SPACE(1)
+ 'ONLINE=OFF' + CHAR(44) + SPACE(1)
+ 'MAXDOP=2' + ')'
END
ELSE
BEGIN
Set @command = 
'Alter Index ' + @IndexName 
+ ' on ' + @dbName + '.[' + @OwnerName + '].[' + @TableName + ']' 
+ ' REORGANIZE  ' 

END
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
BEGIN TRY
SET @@MESSAGE3 = @command
 exec sp_executesql @command
 set @@MESSAGE = 'REINDEXING SUCCEEDED: ' + @IndexName + ' on ' + @dbName + '.[' + @OwnerName + '].[' + @TableName + ']' + ' FILLFACTOR=' + RTRIM(@FillFactor) 
 EXEC master..xp_logevent 60000, @@MESSAGE, informational
 END TRY
 BEGIN CATCH
 SET @@MESSAGE2 = 'REINDEXING FAILED FOR INDEX COMMAND: ' + @@MESSAGE3 + ' ERROR: ' + ERROR_MESSAGE()
EXEC master..xp_logevent 60000, @@MESSAGE2, informational
RAISERROR(@@MESSAGE, 16, 1)
END CATCH
END -- if @flag = 1
END -- if @Test_Run_Flag = 0
--write to logs


-- 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 -- Index Build While @Counter

select @RecoveryModelFinish = recovery_model_desc
FROM sys.databases where '['+name+']' = @dbName

if @RecoveryModelFinish <> @RecoveryModelStart and @dbName not in ('[Master]', '[Model]', '[MSDB]', '[TEMPDB]', '[SSISDB]')
begin
SET @command = 'ALTER DATABASE ' + @DBNAME + ' SET RECOVERY '+ @RecoveryModelStart
EXEC (@command)
SET @command = null
end

-- 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
drop table #indstats
-- 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













USE [DBA]
GO

/****** Object:  StoredProcedure [dbo].[DBA_dbreindex_history_load]    Script Date: 1/22/2014 4:42:34 PM ******/SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE  [dbo].[DBA_dbreindex_history_load]

(@dbNamesysname = NULL, 
@retentionint = 30,
@retention_unit varchar(10) = 'day',
@ExlusionListvarchar(2000) = null
)

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: 2014/01/22 14:30:00
' 
SET @dbName = '['+ REPLACE(REPLACE(@DBNAME,'[',''),']','') +']'


-- 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




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

DECLARE @ExlusionsTable Table (DBName varchar(128))
DECLARE @c_pos int
DECLARE @n_pos int
DECLARE @l_pos int

IF  NOT (@ExlusionList is null OR LTRIM(RTRIM(@ExlusionList)) = '')
BEGIN
IF CHARINDEX(',',@ExlusionList) = 0 or CHARINDEX(',',@ExlusionList) is null
BEGIN
insert into @ExlusionsTable (DBName) values ('['+ REPLACE(REPLACE(REPLACE(UPPER(@ExlusionList),' ',''),'[',''),']','') +']')
END
ELSE
BEGIN
set @c_pos = 0;
set @n_pos = CHARINDEX(',',@ExlusionList,@c_pos)

while @n_pos > 0
BEGIN
insert into @ExlusionsTable (DBName) values (SUBSTRING(@ExlusionList,@c_pos+1,@n_pos - @c_pos-1))
set @c_pos = @n_pos
set @l_pos = @n_pos
set @n_pos = CHARINDEX(',',@ExlusionList,@c_pos+1)
END

insert into @ExlusionsTable (DBName) values (SUBSTRING(@ExlusionList,@l_pos+1,4000))

UPDATE @ExlusionsTable
SET DBName = '['+ REPLACE(REPLACE(REPLACE(UPPER(DBName),' ', ''),'[',''),']','') +']'
END
IF (SELECT COUNT(*) FROM @ExlusionsTable WHERE DBNAME = @dbName) > 0
BEGIN
GOTO job_abend
END
END -- EXIT IF DATABASE IS EXCLUDED

DECLARE @MinDate datetime
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


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



USE [msdb]
GO

/****** Object:  Job [Dynamic Reindex job]    Script Date: 04/13/2013 09:47:53 ******/BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 04/13/2013 09:47:53 ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Dynamic Reindex job', 
@enabled=0, 
@notify_level_eventlog=2, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'No description available.', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Dynamically Reindex All Databases]    Script Date: 04/13/2013 09:47:53 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Dynamically Reindex All Databases', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=3, 
@on_success_step_id=0, 
@on_fail_action=3, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'exec sys.sp_MSforeachdb @command1="EXEC DBA.[dbo].[DBA_dbReindex] ''?'', 100, ''percent'', 90, 3, 0"', 
@database_name=N'DBA', 
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Load Index History]    Script Date: 04/13/2013 09:47:53 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Load Index History', 
@step_id=2, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'exec sys.sp_MSforeachdb @command1="EXEC [dbo].[DBA_dbreindex_history_load] ''?'', 30, ''day''"', 
@database_name=N'DBA', 
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule 1', 
@enabled=1, 
@freq_type=4, 
@freq_interval=1, 
@freq_subday_type=1, 
@freq_subday_interval=1, 
@freq_relative_interval=0, 
@freq_recurrence_factor=0, 
@active_start_date=20071101, 
@active_end_date=99991231, 
@active_start_time=10000, 
@active_end_time=235959, 
@schedule_uid=N'09a4ff5a-77ef-4109-a13a-683f11b16321'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

USE [msdb]
GO

/****** Object:  Job [Dynamic_Offline_Reindexing]    Script Date: 2/14/2014 4:27:19 PM ******/BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 2/14/2014 4:27:19 PM ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Dynamic_Offline_Reindexing', 
@enabled=0, 
@notify_level_eventlog=2, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'No description available.', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Dynamically Offline Reindex All Databases]    Script Date: 2/14/2014 4:27:19 PM ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Dynamically Offline Reindex All Databases', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=3, 
@on_success_step_id=0, 
@on_fail_action=3, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'exec sys.sp_MSforeachdb @command1="EXEC DBA.[dbo].[DBA_dbReindex] ''?'', 100, ''percent'', 91, 3, 0, 1, 1, 250, null"

--EXECUTE @RC = [dbo].[DBA_dbReindex] 
--   @dbName = ?
--  ,@limit = 100
--  ,@limit_type = ''percent''
--  ,@ScanDensity_Threshold = 91
--  ,@FillFactor_Age_Limit = 3
--  ,@Test_Run_Flag = 0
--  ,@offline = 1
--  ,@MinimalLogging = 1
--  ,@MinimumPages = 250
--  ,@ExlusionList = null', 
@database_name=N'DBA', 
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Load Index History]    Script Date: 2/14/2014 4:27:19 PM ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Load Index History', 
@step_id=2, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'exec sys.sp_MSforeachdb @command1="EXEC [dbo].[DBA_dbreindex_history_load] ''?'', 30, ''day''"', 
@database_name=N'DBA', 
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO