Comma delimeted

Comma delimited info for patients should not allow for ", jr" or ", sr". This messed up my night. Ugh, 5 records had this type of data in the lastname column. When code saw this it shifted the column to value mapping to the right by one. The mapping was being done in a SQL DTS package.

Deductive reasoning:

Code does what you tell it. You tell it what you think it should do. If the code fails they you didn't think hard enough.

Already found a few bugs, here's latest update

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_IndexMaintenance] Script Date: 04/01/2008 13:25:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* =============================================
-- Author: MK
-- Create date: 20080401
-- Description: Doesn't take partietions into account
Note create tabe "TrackFragmentation" and update
accordingly currently "[SCHEDULER].dbo.TrackFragmentation"
============================================= */
ALTER PROCEDURE [dbo].[sp_IndexMaintenance]
@DBName VarChar(50)
, @ViewType VarChar(20) = NULL --// Recent | Change
, @Rebuild_CIX bit = 0 --// 0 | 1 = True
, @Rebuild_NCIX bit = 0 --// 0 | 1 = True
, @Rebuild_ViewExecuteLoad VARCHAR(20) = 'View' --// View | Execute | Load
, @Rebuild_UsingLast BIT = 0
, @MinRows INT = 1 --// 1 - x
, @MaxRows INT = 500000000 --// 1 - x
, @MinFragPercent INT = 1 --// 1 - 100
, @MaxFragPercent INT = 90 --// 1 - 100
, @MinDensity INT = 1 --// 1- 100
, @MaxDensity INT = 90 --// 1- 100
, @ErrorMsg varchar(4000) output

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE
@DBID INT
, @DateCreated DateTime
, @IndexType VarChar(100)
, @MaintenanceAction VarChar(100)
, @RunID INT
, @Counter INT
, @Count INT
, @sql_Action VARCHAR(8000)
, @Use VARCHAR(255)
, @ErrorMessage NVARCHAR(4000)
, @Msg NVARCHAR(1000)
, @RC_SUCCESSFUL INT
, @RC_FAILED INT
, @RowsAffected INT
, @Error INT

DECLARE @tmp TABLE (idx int identity(1,1), sql_action VarChar(8000), TableName VarChar(255))

SELECT
@RC_SUCCESSFUL =320
,@RC_FAILED = -1
,@RowsAffected = 0
,@MaintenanceAction = 'NOTHING'
,@Msg = ''

/*
Perform validation
*/

SELECT @MaintenanceAction = 'LOAD' --//Default

--// What do you want to do
IF LEN(ISNULL(@ViewType,'~')) > 1
BEGIN
IF @ViewType NOT IN ('RECENT','CHANGE')
BEGIN
SELECT @Msg = 'Invalid @ViewType: ' + @ViewType
GOTO PROBLEM
END

SELECT @MaintenanceAction = 'VIEW'
END
ELSE IF (@Rebuild_CIX = 1 OR @Rebuild_NCIX = 1)
BEGIN

IF ISNULL(@Rebuild_ViewExecuteLoad,'~') NOT IN ('VIEW','EXECUTE','LOAD')
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid @Rebuild_ViewOrExecute: ' + @Rebuild_ViewExecuteLoad

IF NOT ISNULL(@MinFragPercent,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MinFragPercent' + LTRIM(str(@MinFragPercent))

IF NOT ISNULL(@MaxFragPercent,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MaxFragPercent' + LTRIM(str(@MaxFragPercent))

IF NOT ISNULL(@MinDensity,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MinDensity' + LTRIM(str(@MinDensity))

IF NOT ISNULL(@MaxDensity,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MaxDensity' + LTRIM(str(@MaxDensity))

IF @Rebuild_CIX = 1 AND @Rebuild_NCIX = 1
SELECT @Msg = @Msg + char(10) + char(13) + 'You can only Rebuild either NONCLUSTERED or CLUSTERED, not both'

IF ISNULL(@MinRows,0) <= 0
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MinRows' + LTRIM(str(@MinRows))

IF ISNULL(@MaxRows,0) <= 0
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MaxRows' + LTRIM(str(@MaxRows))



IF LEN(ISNULL(@Msg,'')) > 1 GOTO PROBLEM

END
ELSE IF ISNULL(@Rebuild_ViewExecuteLoad,'~') <> 'LOAD'
BEGIN
SELECT @Msg = 'What do you want to do?'
GOTO PROBLEM
END

SELECT
@DBID = DB_ID(@DBName)
,@DateCreated = GetDate()

IF @DBID IS NULL
BEGIN
SELECT @Msg = 'Invalid : @DBName' + @DBName
GOTO PROBLEM
END

IF @MaintenanceAction = 'VIEW'
BEGIN
IF @ViewType = 'CHANGE'
BEGIN
-- View Frag change
SELECT
A.DBName
, A.TableName
, A.IndexName
, A.IndexType
, CAST(ROUND(A.current_density_percentage,2) AS FLOAT) [Now Dens]
, CAST(ROUND( B.current_density_percentage,2) AS FLOAT) [Prev Dens]
, CAST(ROUND(A.current_fragmentation_percentage,2) AS FLOAT) [Now %Frag]
, CAST(ROUND(B.current_fragmentation_percentage,2) AS FLOAT) [Prev %Frag]
, CAST(ROUND(A.MBUsed,2) AS FLOAT) [Now MB]
, CAST(ROUND(B.MBUsed,2) AS FLOAT) [Prev MB]
, A.Rows [Now Rows]
, B.Rows [Prev Rows]
,Max(A.RunID) [Now RunID]
,A.DateCreated [Now RunTime]
,Max(B.RunID) [Prev RunID]
,B.DateCreated [Prev RunTime]
FROM
[SCHEDULER].dbo.TrackFragmentation A
LEFT JOIN [SCHEDULER].dbo.TrackFragmentation B ON B.TableName = A.Tablename AND B.IndexName = A.IndexName
WHERE
A.RunID > B.RunID
AND A.DBName = @DBName
GROUP BY A.DBName
, A.TableName
, A.IndexName
, A.IndexType
, CAST(ROUND(A.current_density_percentage,2) AS FLOAT)
, CAST(ROUND( B.current_density_percentage,2) AS FLOAT)
, CAST(ROUND(A.current_fragmentation_percentage,2) AS FLOAT)
, CAST(ROUND(B.current_fragmentation_percentage,2) AS FLOAT)
, CAST(ROUND(A.MBUsed,2) AS FLOAT)
, CAST(ROUND(B.MBUsed,2) AS FLOAT)
, A.Rows
, B.Rows
, A.DateCreated
, B.DateCreated
Order by Max(A.RunID) DESC, MAX(B.RunID) DESC, A.IndexType DESC,A.[Rows] ASC, A.TableName, A.IndexName
END
ELSE
BEGIN
SELECT
A.DBName
, A.TableName
, A.IndexName
, A.IndexType
, CAST(ROUND(A.current_density_percentage,2) AS FLOAT) [Now Dens]
, CAST(ROUND(A.current_fragmentation_percentage,2) AS FLOAT) [Now %Frag]
, CAST(ROUND(A.MBUsed,2) AS FLOAT) [Now MB]
, A.Rows [Now Rows]
, A.RunID [Now RunID]
,A.DateCreated [Now RunTime]
FROM
[SCHEDULER].dbo.TrackFragmentation A
WHERE
A.RunID = (SELECT Max(RunID) FROM [SCHEDULER].dbo.TrackFragmentation WHERE DBName = @DBName)
Order by A.IndexType DESC,A.[Rows] ASC, A.TableName, A.IndexName
END

GOTO EXIT_PROC
END

IF @Rebuild_UsingLast = 1
BEGIN
SELECT
@RunID = MAX(RunID)
FROM
[SCHEDULER].dbo.TrackFragmentation WHERE DBName = @DBName

IF @@RowCount = 1 GOTO REBUILD_USINGLAST

SELECT @Msg = @Msg + char(10) + char(13) + 'There is not existing data for this DB ' + @DBName
GOTO PROBLEM
END

--// Load TrackFragmentation
SELECT
@RunID = MAX(RunID) + 1
FROM
[SCHEDULER].dbo.TrackFragmentation

IF @RunID IS NULL SELECT @RunID = 1

PRINT 'Loading TrackFragmentation'

SELECT @sql_action = '
USE ' + QUOTENAME(@DBName) + '
IF object_id(''tempdb..##tmpSysIndex'') IS NOT NULL
BEGIN
DROP TABLE ##tmpSysIndex
END
SELECT OBJECT_NAME(object_id) TableName, * INTO ##tmpSysIndex FROM ' + QUOTENAME(@DBName) + '.sys.indexes WHERE [Name] is not null

IF object_id(''tempdb..##tmp_dboSysindex'') IS NOT NULL
BEGIN
DROP TABLE ##tmp_dboSysindex
END
SELECT * INTO ##tmp_dboSysindex FROM ' + QUOTENAME(@DBName) + '.dbo.sysindexes WHERE groupid >0'

EXEC(@sql_action)

INSERT INTO [SCHEDULER].dbo.TrackFragmentation(
DBName
, TableName
, IndexName
, IndexType
, DataStoreType
, partition_number
, current_density_percentage
, current_fragmentation_percentage
, MBUsed
, Rows
, [object_id]
, index_id
, DateCreated
, RunID
)
SELECT
DB_NAME(@DBID) --// AS DBName
,I.TableName --// AS TableName
,I.name AS [IndexName]
,I.type_desc --// AS [IndexType] REPLACE(p.index_type_desc,' INDEX','')
,p.[alloc_unit_type_desc] --// AS DataStoreType
,CAST(p.[partition_number] AS varchar(10)) --// AS [partition_number]
,p.[avg_page_space_used_in_percent] --// AS [current_density_percentage]
,p.[avg_fragmentation_in_percent] --// AS current_fragmentation_percentage
,CASE WHEN page_count <= 8 THEN 0 ELSE (page_count * 8.0 / 1024.0) END --// AS [MBUsed]
,(SELECT top 1 rows FROM ##tmp_dboSysindex WHERE id = p.object_id AND indid < 2) --// [Rows]
,P.[object_id]
,P.[index_id]
,@DateCreated --// AS DateCreated
,@RunID --// AS RunID
--INTO TrackFragmentation --// If you need to re-create
FROM
sys.dm_db_index_physical_stats( @DBID, NULL, NULL, NULL, 'SAMPLED') p
JOIN ##tmpSysIndex i (NOLOCK) ON I.[object_id] = p.[object_id] AND I.[index_id] = p.[index_id]
WHERE
p.index_id > 0

PRINT 'Loaded: ' + LTRIM(STR(@@ROWCOUNT)) + ' records'

REBUILD_USINGLAST:

--// When I try to exclude this in main query it takes longer
UPDATE [SCHEDULER].dbo.TrackFragmentation
SET RunOnline = 0
WHERE [object_ID] IN (SELECT [object_id] FROM [SCHEDULER].dbo.TrackFragmentation T WHERE DataStoreType = 'LOB_DATA')

DELETE FROM [SCHEDULER].dbo.TrackFragmentation WHERE DataStoreType = 'LOB_DATA' AND RunID = @RunID

--// Drop Temp tables
IF object_id('tempdb..##tmpSysIndex') IS NOT NULL DROP TABLE ##tmpSysIndex
IF object_id('tempdb..##tmp_dboSysindex') IS NOT NULL DROP TABLE ##tmp_dboSysindex

IF @Rebuild_ViewExecuteLoad = 'LOAD' GOTO EXIT_PROC

IF @Rebuild_NCIX = 1
BEGIN
SET @IndexType = 'NONCLUSTERED'
END
ELSE
BEGIN
SET @IndexType = 'CLUSTERED'
END

SELECT TOP 1
@Use = 'USE ' + QUOTENAME(DBName)
FROM
[SCHEDULER].dbo.TrackFragmentation (NOLOCK)
WHERE
RunID = @RunID

INSERT INTO @tmp
SELECT @Use, NULL

INSERT INTO @tmp
SELECT
'PRINT ''--//' + QUOTENAME(DBName) + '.' + QUOTENAME(TableName) + '..' + QUOTENAME(IndexName) +
' SIZE: ' + Ltrim(str([MBUsed])) + '(mb) ' +
' Rows: ' + Ltrim(str([Rows])) +
' %Frag: ' + Ltrim(str(Cast(round(current_fragmentation_percentage,0) as int))) +
' Density: ' + Ltrim(str(Cast(round(current_density_percentage,0) as int))) + '''' + CHAR(13) + CHAR(10)
+ CASE WHEN [Rows] > 1000000 THEN 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(DBName) + '..' + QUOTENAME(TableName) + ' REORGANIZE'
ELSE 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(DBName) + '..' + QUOTENAME(TableName) + ' REBUILD WITH (ONLINE=' + CASE WHEN RunOnline = 0 THEN 'OFF' ELSE 'ON' END +', SORT_IN_TEMPDB=ON) ' END + '
UPDATE STATISTICS ' + QUOTENAME(TableName) + QUOTENAME(IndexName)
, QUOTENAME(TableName)
FROM
[SCHEDULER].dbo.TrackFragmentation (NOLOCK)
WHERE
(
(current_fragmentation_percentage BETWEEN @MinFragPercent AND @MaxFragPercent)
OR
(current_density_percentage BETWEEN @MinDensity AND @MaxDensity)
)
AND Rows BETWEEN @MinRows AND @MaxRows
AND IndexType = @IndexType
AND RunID = @RunID
ORDER BY
IndexType DESC
,Rows ASC
,TableName

INSERT INTO @tmp
SELECT
'EXEC sp_recompile ''' + TableName + ''''+ CHAR(13) + CHAR(10),NULL
FROM
@tmp
GROUP BY
TableName

SELECT @Counter = 0, @Count = Count(*) FROM @tmp

WHILE ISNULL(@Counter,0) < @Count
BEGIN
SELECT @Counter = @Counter + 1

SELECT @sql_Action = sql_Action FROM @tmp WHERE IDX = @Counter

IF @Rebuild_ViewExecuteLoad = 'Execute'
BEGIN
SELECT @sql_Action = @Use + char(10) + char(13) + @sql_Action
EXEC(@sql_Action)
END
ELSE
BEGIN
PRINT @sql_Action
END
END


EXIT_PROC:
RETURN(@RC_SUCCESSFUL)

PROBLEM:
SELECT @ErrorMsg = 'Stored Procedure: sp_IndexMaintenance
Error Message: ' + @Msg + '
Input Parameter(s):
@DBName = ' + isnull( CONVERT(nvarchar,@DBName), '' ) + '
@ViewType = ' + isnull( CONVERT(nvarchar,@ViewType), '' ) + '
@Rebuild_CIX = ' + isnull( CONVERT(nvarchar,@Rebuild_CIX), '' ) + '
@Rebuild_NCIX = ' + isnull( CONVERT(nvarchar,@Rebuild_NCIX), '' ) + '
@Rebuild_ViewExecuteLoad = ' + isnull( CONVERT(nvarchar,@Rebuild_ViewExecuteLoad), '' ) + '
@MinFragPercent = ' + isnull( CONVERT(nvarchar,@MinFragPercent), '' ) + '
@MaxFragPercent = ' + isnull( CONVERT(nvarchar,@MaxFragPercent), '' ) + '
@MinDensity = ' + isnull( CONVERT(nvarchar,@MinDensity), '' ) + '
@MaxDensity = ' + isnull( CONVERT(nvarchar,@MaxDensity), '' )
SELECT @ErrorMsg
RAISERROR(@ErrorMsg, 16,1) WITH LOG
RETURN(@RC_FAILED)

END

Here's the Create for TrackFragmentation table

USE [scheduler]
GO
/****** Object: Table [dbo].[TrackFragmentation] Script Date: 04/01/2008 19:06:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TrackFragmentation](
[DBName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TableName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IndexName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IndexType] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DataStoreType] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[partition_number] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[current_density_percentage] [float] NULL,
[current_fragmentation_percentage] [float] NULL,
[MBUsed] [numeric](29, 7) NULL,
[Rows] [int] NULL,
[object_id] [int] NULL,
[index_id] [int] NULL,
[RunOnline] [bit] NULL CONSTRAINT [DF_TrackFragmentation_RunOnline] DEFAULT ((1)),
[DateCreated] [datetime] NULL,
[RunID] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

mmmm Nice tidy interface

Oh my!!! A sense of accomplishment. Now I can do just about anything with my indexes, schedule rebuild, monitor changes, create an alert if the delta is out of line.

DECLARE @return_value int,
@ErrorMsg varchar(4000)

EXEC @return_value = [dbo].[sp_IndexMaintenance]
@DBName = N'MyDB',
@ViewType = N'',
@Rebuild_CIX = 0,
@Rebuild_NCIX = 1,
@Rebuild_ViewExecuteLoad = 'LOAD',
@Rebuild_UsingLast = 0,
@MinRows = 1,
@MaxRows = 1000000,
@MinFragPercent = 3,
@MaxFragPercent = 90,
@MinDensity = 1,
@MaxDensity = 90,
@ErrorMsg = @ErrorMsg OUTPUT

I think I got it sp_IndexMaintenance

not entirely an original work, see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2865707&SiteID=1.

But I'm not very fond of just copy, paste, and pray. I wanted to ensure I fully understood what was going on. Also adapted it to fit my specific needs. But I would also hope this is a wee bit easier to grasp. Let me know if i'm missed something.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_IndexMaintenance] Script Date: 04/01/2008 13:25:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* =============================================
-- Author: BOBMIHADA
-- Create date: 20080401
-- Description: Doesn't take partietions into account
Note create tabe "TrackFragmentation" and update
accordingly currently "[SCHEDULER].dbo.TrackFragmentation"
============================================= */
ALTER PROCEDURE [dbo].[sp_IndexMaintenance]
@DBName VarChar(50)
, @ViewType VarChar(20) = NULL --// Recent | Change
, @Rebuild_CIX bit = 0 --// 0 | 1 = True
, @Rebuild_NCIX bit = 0 --// 0 | 1 = True
, @Rebuild_ViewExecuteLoad VARCHAR(20) = 'View' --// View | Execute | Load
, @Rebuild_UsingLast BIT = 0
, @MinRows INT = 1 --// 1 - x
, @MaxRows INT = 500000000 --// 1 - x
, @MinFragPercent INT = 1 --// 1 - 100
, @MaxFragPercent INT = 90 --// 1 - 100
, @MinDensity INT = 1 --// 1- 100
, @MaxDensity INT = 90 --// 1- 100
, @ErrorMsg varchar(4000) output

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE
@DBID INT
, @DateCreated DateTime
, @IndexType VarChar(100)
, @MaintenanceAction VarChar(100)
, @RunID INT
, @Counter INT
, @Count INT
, @sql_Action VARCHAR(8000)
, @Use VARCHAR(255)
, @ErrorMessage NVARCHAR(4000)
, @Msg NVARCHAR(1000)
, @RC_SUCCESSFUL INT
, @RC_FAILED INT
, @RowsAffected INT
, @Error INT

DECLARE @tmp TABLE (idx int identity(1,1), sql_action VarChar(8000), TableName VarChar(255))

SELECT
@RC_SUCCESSFUL =320
,@RC_FAILED = -1
,@RowsAffected = 0
,@MaintenanceAction = 'NOTHING'
,@Msg = ''

/*
Perform validation
*/

SELECT @MaintenanceAction = 'LOAD' --//Default

--// What do you want to do
IF LEN(ISNULL(@ViewType,'~')) > 1
BEGIN
IF @ViewType NOT IN ('RECENT','CHANGE')
BEGIN
SELECT @Msg = 'Invalid @ViewType: ' + @ViewType
GOTO PROBLEM
END

SELECT @MaintenanceAction = 'VIEW'
END
ELSE IF (@Rebuild_CIX = 1 OR @Rebuild_NCIX = 1)
BEGIN

IF ISNULL(@Rebuild_ViewExecuteLoad,'~') NOT IN ('VIEW','EXECUTE','LOAD')
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid @Rebuild_ViewOrExecute: ' + @Rebuild_ViewExecuteLoad

IF NOT ISNULL(@MinFragPercent,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MinFragPercent' + LTRIM(str(@MinFragPercent))

IF NOT ISNULL(@MaxFragPercent,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MaxFragPercent' + LTRIM(str(@MaxFragPercent))

IF NOT ISNULL(@MinDensity,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MinDensity' + LTRIM(str(@MinDensity))

IF NOT ISNULL(@MaxDensity,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MaxDensity' + LTRIM(str(@MaxDensity))

IF @Rebuild_CIX = 1 AND @Rebuild_NCIX = 1
SELECT @Msg = @Msg + char(10) + char(13) + 'You can only Rebuild either NONCLUSTERED or CLUSTERED, not both'

IF ISNULL(@MinRows,0) <= 0
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MinRows' + LTRIM(str(@MinRows))

IF ISNULL(@MaxRows,0) <= 0
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MaxRows' + LTRIM(str(@MaxRows))



IF LEN(ISNULL(@Msg,'')) > 1 GOTO PROBLEM

END
ELSE IF ISNULL(@Rebuild_ViewExecuteLoad,'~') <> 'LOAD'
BEGIN
SELECT @Msg = 'What do you want to do?'
GOTO PROBLEM
END

SELECT
@DBID = DB_ID(@DBName)
,@DateCreated = GetDate()

IF @DBID IS NULL
BEGIN
SELECT @Msg = 'Invalid : @DBName' + @DBName
GOTO PROBLEM
END

IF @MaintenanceAction = 'VIEW'
BEGIN
IF @ViewType = 'CHANGE'
BEGIN
-- View Frag change
SELECT
A.DBName
, A.TableName
, A.IndexName
, A.IndexType
, CAST(ROUND(A.current_density_percentage,2) AS FLOAT) [Now Dens]
, CAST(ROUND( B.current_density_percentage,2) AS FLOAT) [Prev Dens]
, CAST(ROUND(A.current_fragmentation_percentage,2) AS FLOAT) [Now %Frag]
, CAST(ROUND(B.current_fragmentation_percentage,2) AS FLOAT) [Prev %Frag]
, CAST(ROUND(A.MBUsed,2) AS FLOAT) [Now MB]
, CAST(ROUND(B.MBUsed,2) AS FLOAT) [Prev MB]
, A.Rows [Now Rows]
, B.Rows [Prev Rows]
,Max(A.RunID) [Now RunID]
,A.DateCreated [Now RunTime]
,Max(B.RunID) [Prev RunID]
,B.DateCreated [Prev RunTime]
FROM
[SCHEDULER].dbo.TrackFragmentation A
LEFT JOIN [SCHEDULER].dbo.TrackFragmentation B ON B.TableName = A.Tablename AND B.IndexName = A.IndexName
WHERE
A.RunID > B.RunID
AND A.DBName = @DBName
GROUP BY A.DBName
, A.TableName
, A.IndexName
, A.IndexType
, CAST(ROUND(A.current_density_percentage,2) AS FLOAT)
, CAST(ROUND( B.current_density_percentage,2) AS FLOAT)
, CAST(ROUND(A.current_fragmentation_percentage,2) AS FLOAT)
, CAST(ROUND(B.current_fragmentation_percentage,2) AS FLOAT)
, CAST(ROUND(A.MBUsed,2) AS FLOAT)
, CAST(ROUND(B.MBUsed,2) AS FLOAT)
, A.Rows
, B.Rows
, A.DateCreated
, B.DateCreated
Order by A.IndexType DESC,A.[Rows] ASC, A.TableName, A.IndexName
END
ELSE
BEGIN
SELECT
A.DBName
, A.TableName
, A.IndexName
, A.IndexType
, CAST(ROUND(A.current_density_percentage,2) AS FLOAT) [Now Dens]
, CAST(ROUND(A.current_fragmentation_percentage,2) AS FLOAT) [Now %Frag]
, CAST(ROUND(A.MBUsed,2) AS FLOAT) [Now MB]
, A.Rows [Now Rows]
, A.RunID [Now RunID]
,A.DateCreated [Now RunTime]
FROM
[SCHEDULER].dbo.TrackFragmentation A
WHERE
A.DBName =@DBName
AND A.RunID = (SELECT Max(RunID) FROM [SCHEDULER].dbo.TrackFragmentation WHERE A.DBName = @DBName)
Order by A.IndexType DESC,A.[Rows] ASC, A.TableName, A.IndexName
END

GOTO EXIT_PROC
END

IF @Rebuild_UsingLast = 1
BEGIN
SELECT
@RunID = MAX(RunID)
FROM
[SCHEDULER].dbo.TrackFragmentation WHERE DBName = @DBName

IF @@RowCount = 1 GOTO REBUILD_USINGLAST

SELECT @Msg = @Msg + char(10) + char(13) + 'There is not existing data for this DB ' + @DBName
GOTO PROBLEM
END

--// Load TrackFragmentation
SELECT
@RunID = MAX(RunID) + 1
FROM
[SCHEDULER].dbo.TrackFragmentation

IF @RunID IS NULL SELECT @RunID = 1

PRINT 'Loading TrackFragmentation'

SELECT @sql_action = '
USE ' + QUOTENAME(@DBName) + '
IF object_id(''tempdb..##tmpSysIndex'') IS NOT NULL
BEGIN
DROP TABLE ##tmpSysIndex
END
SELECT OBJECT_NAME(object_id) TableName, * INTO ##tmpSysIndex FROM ' + QUOTENAME(@DBName) + '.sys.indexes WHERE [Name] is not null

IF object_id(''tempdb..##tmp_dboSysindex'') IS NOT NULL
BEGIN
DROP TABLE ##tmp_dboSysindex
END
SELECT * INTO ##tmp_dboSysindex FROM ' + QUOTENAME(@DBName) + '.dbo.sysindexes WHERE groupid >0'

EXEC(@sql_action)

INSERT INTO [SCHEDULER].dbo.TrackFragmentation(
DBName
, TableName
, IndexName
, IndexType
, DataStoreType
, partition_number
, current_density_percentage
, current_fragmentation_percentage
, MBUsed
, Rows
, [object_id]
, index_id
, DateCreated
, RunID
)
SELECT
DB_NAME(@DBID) --// AS DBName
,I.TableName --// AS TableName
,I.name AS [IndexName]
,I.type_desc --// AS [IndexType] REPLACE(p.index_type_desc,' INDEX','')
,p.[alloc_unit_type_desc] --// AS DataStoreType
,CAST(p.[partition_number] AS varchar(10)) --// AS [partition_number]
,p.[avg_page_space_used_in_percent] --// AS [current_density_percentage]
,p.[avg_fragmentation_in_percent] --// AS current_fragmentation_percentage
,CASE WHEN page_count <= 8 THEN 0 ELSE (page_count * 8.0 / 1024.0) END --// AS [MBUsed]
,(SELECT top 1 rows FROM ##tmp_dboSysindex WHERE id = p.object_id AND indid < 2) --// [Rows]
,P.[object_id]
,P.[index_id]
,@DateCreated --// AS DateCreated
,@RunID --// AS RunID
--INTO TrackFragmentation --// If you need to re-create
FROM
sys.dm_db_index_physical_stats( @DBID, NULL, NULL, NULL, 'SAMPLED') p
JOIN ##tmpSysIndex i (NOLOCK) ON I.[object_id] = p.[object_id] AND I.[index_id] = p.[index_id]
WHERE
p.index_id > 0

PRINT 'Loaded: ' + LTRIM(STR(@@ROWCOUNT)) + ' records'

REBUILD_USINGLAST:

--// When I try to exclude this in main query it takes longer
UPDATE [SCHEDULER].dbo.TrackFragmentation
SET RunOnline = 0
WHERE [object_ID] IN (SELECT [object_id] FROM [SCHEDULER].dbo.TrackFragmentation T WHERE DataStoreType = 'LOB_DATA')

DELETE FROM [SCHEDULER].dbo.TrackFragmentation WHERE DataStoreType = 'LOB_DATA' AND RunID = @RunID

--// Drop Temp tables
IF object_id('tempdb..##tmpSysIndex') IS NOT NULL DROP TABLE ##tmpSysIndex
IF object_id('tempdb..##tmp_dboSysindex') IS NOT NULL DROP TABLE ##tmp_dboSysindex

IF @Rebuild_ViewExecuteLoad = 'LOAD' GOTO EXIT_PROC

IF @Rebuild_NCIX = 1
BEGIN
SET @IndexType = 'NONCLUSTERED'
END
ELSE
BEGIN
SET @IndexType = 'CLUSTERED'
END

SELECT TOP 1
@Use = 'USE ' + QUOTENAME(DBName)
FROM
[SCHEDULER].dbo.TrackFragmentation (NOLOCK)
WHERE
RunID = @RunID

INSERT INTO @tmp
SELECT @Use, NULL

INSERT INTO @tmp
SELECT
'PRINT ''--//' + QUOTENAME(DBName) + '.' + QUOTENAME(TableName) + '..' + QUOTENAME(IndexName) +
' SIZE: ' + Ltrim(str([MBUsed])) + '(mb) ' +
' Rows: ' + Ltrim(str([Rows])) +
' %Frag: ' + Ltrim(str(Cast(round(current_fragmentation_percentage,0) as int))) +
' Density: ' + Ltrim(str(Cast(round(current_density_percentage,0) as int))) + '''' + CHAR(13) + CHAR(10)
+ CASE WHEN [Rows] > 1000000 THEN 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(DBName) + '..' + QUOTENAME(TableName) + ' REORGANIZE'
ELSE 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(DBName) + '..' + QUOTENAME(TableName) + ' REBUILD WITH (ONLINE=' + CASE WHEN RunOnline = 0 THEN 'OFF' ELSE 'ON' END +', SORT_IN_TEMPDB=ON) ' END + '
UPDATE STATISTICS ' + QUOTENAME(TableName) + QUOTENAME(IndexName)
, QUOTENAME(TableName)
FROM
[SCHEDULER].dbo.TrackFragmentation (NOLOCK)
WHERE
(
(current_fragmentation_percentage BETWEEN @MinFragPercent AND @MaxFragPercent)
OR
(current_density_percentage BETWEEN @MinDensity AND @MaxDensity)
)
AND Rows BETWEEN @MinRows AND @MaxRows
AND IndexType = @IndexType
AND RunID = @RunID
ORDER BY
IndexType DESC
,Rows ASC
,TableName

INSERT INTO @tmp
SELECT
'EXEC sp_recompile ''' + TableName + ''''+ CHAR(13) + CHAR(10),NULL
FROM
@tmp
GROUP BY
TableName

SELECT @Counter = 0, @Count = Count(*) FROM @tmp

WHILE ISNULL(@Counter,0) < @Count
BEGIN
SELECT @Counter = @Counter + 1

SELECT @sql_Action = sql_Action FROM @tmp WHERE IDX = @Counter

IF @Rebuild_ViewExecuteLoad = 'Execute'
BEGIN
SELECT @sql_Action = @Use + char(10) + char(13) + @sql_Action
EXEC(@sql_Action)
END
ELSE
BEGIN
PRINT @sql_Action
END
END


EXIT_PROC:
RETURN(@RC_SUCCESSFUL)

PROBLEM:
SELECT @ErrorMsg = 'Stored Procedure: sp_IndexMaintenance
Error Message: ' + @Msg + '
Input Parameter(s):
@DBName = ' + isnull( CONVERT(nvarchar,@DBName), '' ) + '
@ViewType = ' + isnull( CONVERT(nvarchar,@ViewType), '' ) + '
@Rebuild_CIX = ' + isnull( CONVERT(nvarchar,@Rebuild_CIX), '' ) + '
@Rebuild_NCIX = ' + isnull( CONVERT(nvarchar,@Rebuild_NCIX), '' ) + '
@Rebuild_ViewExecuteLoad = ' + isnull( CONVERT(nvarchar,@Rebuild_ViewExecuteLoad), '' ) + '
@MinFragPercent = ' + isnull( CONVERT(nvarchar,@MinFragPercent), '' ) + '
@MaxFragPercent = ' + isnull( CONVERT(nvarchar,@MaxFragPercent), '' ) + '
@MinDensity = ' + isnull( CONVERT(nvarchar,@MinDensity), '' ) + '
@MaxDensity = ' + isnull( CONVERT(nvarchar,@MaxDensity), '' )
SELECT @ErrorMsg
RAISERROR(@ErrorMsg, 16,1) WITH LOG
RETURN(@RC_FAILED)

END

Brain in a daze

CREATE PROCEDURE sp_IndexMaintenance
@DBName VarChar(50)
, @ViewType VarChar(20) = NULL --// Recent | Change
, @Rebuild_CIX bit = 0 --// 0 | 1 = True
, @Rebuild_NCIX bit = 0 --// 0 | 1 = True
, @Rebuild_ViewOrExecute = 'View' --// View | Execute
, @MinFragPercent INT = 1 --// 1 - 100
, @MaxFragPercent INT = 90 --// 1 - 100
, @MinDensity INT = 1 --// 1- 100
, @MaxDensity INT = 90 --// 1- 100
, @ErrorMsg varchar(4000) output