if OBJECT_ID('fpscdb001_system.p_Reindex_Fragmented_Tables','P') is not null
begin
drop proc fpscdb001_system.p_Reindex_Fragmented_Tables;
end
go

create proc fpscdb001_system.p_Reindex_Fragmented_Tables
(	@minimumPageSizeToReindex int = 8,
	@minimumPercentForRebuild decimal(6,2) = 5.0,
	@isOnline bit = 1
)
as
begin
-- script to reindex Footprints Database tables
-- conditionally based on max fragmentation percentage
-- V2.0 Change log 3/1/2016 tbroecke
-- . Added table variable for cursor to avoid locking system tables
-- . Improved error handling to recognize additional errors and report unknown errors
-- . Lowered defaults 

-- You can use this in a scheduled task, you just need the exec statement not the whole procedure recreation
-- Note that you can change default values in declaration, but calling parameters will override the defaults


DECLARE @SchemaName nvarchar(255)
, @TableName nvarchar(255)
, @Max_Fragmentation decimal(6,2)
, @page_count int
, @IndexId int
, @IndexName nvarchar(255)
, @Sql nvarchar(1000)
,@error_number bigint;

declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
 
DECLARE
 @TablesToReindex TABLE
(	SchemaName nvarchar(255),
	TableName nvarchar(255),
	index_id int,
	IndexName nvarchar(255),
	Max_Fragmentation decimal(6,2),
	page_count int);

insert @TablesToReindex
select 
q.SchemaName,
q.TableName
,q.index_id
,q.IndexName
, max(FragmentationPct) as Max_Fragmentation
,max(page_count) as page_count
--
from
(
SELECT
dbstats.database_name,
dbschemas.name as SchemaName,
 dbtables.name as TableName,
  dbindexes.index_id,
  dbindexes.name as IndexName,
 cast(indexstats.avg_fragmentation_in_percent as decimal(6,2))  as FragmentationPct
 ,indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.object_id = indexstats.object_id
INNER JOIN sys.schemas dbschemas on dbtables.schema_id = dbschemas.schema_id
INNER JOIN sys.indexes AS dbindexes ON dbindexes.object_id = indexstats.object_id
AND indexstats.index_id = dbindexes.index_id and alloc_unit_type_desc = 'IN_ROW_DATA'
--
JOIN   (select s.object_id,name as index_name, min(STATS_DATE(s.object_id, S.stats_id)) as last_updated 
		from  sys.stats S 
		group by s.object_id,name ) ss
        ON  Ss.object_id = dbtables.object_id and ss.index_name = dbindexes.name
cross join (select name as database_name,create_date as install_date, is_auto_create_stats_on from sys.databases d where d.database_id = db_id()) DBStats
WHERE indexstats.database_id = DB_ID()
and indexstats.page_count >= @minimumPageSizeToReindex
and dbindexes.name IS NOT NULL
and not (dbschemas.name like '%_WS_%' 
	and (	dbtables.name  like '%_FH_%' or -- dont bother with data mart staging tables
			dbtables.name  like '%_ST_%'
			)) -- dont bother with data mart staging tables
and dbtables.name != 'dyn_table_script'
) q
group by q.SchemaName ,q.tableName,q.index_id,q.IndexName
having max(FragmentationPct) >= @minimumPercentForRebuild
order by q.SchemaName ,q.tableName,q.index_id;

DECLARE TableCursor CURSOR FOR 
select * from @TablesToReindex;


OPEN TableCursor;

FETCH NEXT FROM TableCursor INTO @SchemaName,@TableName,@IndexId,@IndexName,@Max_Fragmentation, @page_count ;
WHILE @@FETCH_STATUS = 0 
BEGIN 
 SET @Sql = 'ALTER INDEX ' + @IndexName + ' ON ' + @SchemaName+ '.' + @TableName + ' REBUILD' +
 case when @isOnline = 1 then ' with ( ONLINE = ON );' else ';' end
 + ' -- Fragmentation: '+ str(@Max_Fragmentation,5,1)  + '%, Page Ct: '+ltrim(str(@page_count)) ;
 print @sql;
 begin try
	EXEC sp_executeSQL @Sql;
 end try
 begin catch
	     
    select @error_number = error_number(),@ErrorMessage = 'ERROR:'+ ltrim(str(@error_number))+ ' ' +ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5))
	, @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
 
	 if @error_number in (1712, 2725)
	 begin
		 Print 'Retrying without Online';
		 SET @Sql = 'ALTER INDEX ' + @IndexName + ' ON ' + @SchemaName+ '.' + @TableName + ' REBUILD;' 
		 + ' -- Fragmentation: '+ str(@Max_Fragmentation,5,1)  + '%, Page Ct: '+ltrim(str(@page_count)) ;
 		 print @sql;
		 EXEC sp_executeSQL @Sql;
	 end
	 else 
	 begin
		raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
		-- throw; -- only works in SQL 2012 and beyond
     end;
 end catch;
 FETCH NEXT FROM TableCursor INTO @SchemaName,@TableName,@IndexId,@IndexName,@Max_Fragmentation, @page_count ;
END;

CLOSE TableCursor; 
DEALLOCATE TableCursor;

end;
go

-- here is the line you need in a scheduled task.  Feel free to change parameters or omit
-- Note that you can change default values in declaration, but calling parameters will override the defaults
exec fpscdb001_system.p_Reindex_Fragmented_Tables /* @minimumPercentForRebuild= 5 ,@isOnline=1, @minimumPageSizeToReindex= 8; */