DECLARE @Threads INT = 5
DECLARE @ReorgThreshold INT = 30
DECLARE @Rows BIGINT = 10000
DECLARE @ByPassGetStats BIT = 0
DECLARE @ByPassDefrag BIT = 0 
DECLARE @DatabaseID INT;
DECLARE @DatabaseName VARCHAR(255);
DECLARE @TableName VARCHAR(255);
DECLARE @SchemaName VARCHAR(255);
DECLARE @ThreadCounter INT;
DECLARE @SQL NVARCHAR(4000);

CREATE	TABLE #IndexFrag (
	DatabaseName VARCHAR(255) NULL, 
	ObjectID INT, 
	IndexID INT, 
	PartitionNumber INT,
	FragmentationPerc FLOAT, 
	Pages INT, 
	Records BIGINT, 
	IndexName VARCHAR(255), 
	SchemaName VARCHAR(255), 
	TableName VARCHAR(255), 
	AllowPageLocks INT);

CREATE TABLE #TableList (
	DatabaseName VARCHAR(255) NULL, 
	SchemaName VARCHAR(255) NULL,
	TableName VARCHAR(255) NULL, 
	Records BIGINT );

IF @ByPassGetStats = 0
BEGIN
	--Get the index fragmentation
	DECLARE ReorgIndexCursor01 CURSOR FOR
	SELECT	[name], database_id
	FROM	master.sys.databases
	WHERE	[name] NOT IN ('master', 'model', 'tempdb') 
	ORDER	BY [name];

	OPEN	ReorgIndexCursor01;

	FETCH	NEXT FROM ReorgIndexCursor01 INTO @DatabaseName, @DatabaseID;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT	INTO #IndexFrag (DatabaseName, ObjectID, IndexID, PartitionNumber, FragmentationPerc, Pages, Records)
		SELECT	@DatabaseName,
				ps.OBJECT_ID,
				ps.index_id,
				ps.partition_number,
				ps.avg_fragmentation_in_percent,
				ps.page_count,
				ps.record_count
		FROM	master.sys.dm_db_index_physical_stats (@DatabaseID, NULL, NULL , NULL, N'SAMPLED') ps
		WHERE	ps.index_id > 0
		OPTION (MaxDop 1);
	
		--Update the table with the schema, table, and index names
		SELECT	@SQL = 'USE [' +  @DatabaseName + '] 

						UPDATE	#IndexFrag
						SET		IndexName = i.name, 
								SchemaName = s.name, 
								TableName = o.name, 
								AllowPageLocks = i.allow_page_locks
						FROM	#IndexFrag ti
								INNER JOIN sys.objects o ON ti.ObjectID = o.object_id
								INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
								INNER JOIN sys.indexes i ON o.object_id = i.object_id
						WHERE	ti.DatabaseName = ' + CHAR(39) + @DatabaseName + CHAR(39) + '
								AND i.index_id = ti.IndexID  ';
		
		EXEC (@SQL);

		FETCH	NEXT FROM ReorgIndexCursor01 INTO @DatabaseName, @DatabaseID;
	END
	CLOSE ReorgIndexCursor01;
	DEALLOCATE ReorgIndexCursor01;
	--Update the PNGCORP_IndexList table
	UPDATE	msdb.dbo.PNGCORP_IndexList 
	SET		FragmentationPerc = f.FragmentationPerc,
			Pages = f.Pages, 
			Records = f.Records, 
			LastChecked = GETDATE()
	FROM	#IndexFrag f
			INNER JOIN msdb.dbo.PNGCORP_IndexList il ON il.DatabaseName = f.DatabaseName
						AND il.ObjectID = f.ObjectID
						AND il.IndexID = f.IndexID
						AND il.PartitionNumber = f.PartitionNumber;
	--Insert new indexes into the PNGCORP_IndexList
	INSERT	INTO msdb.dbo.PNGCORP_IndexList (DatabaseName, ObjectID, IndexID, PartitionNumber, FragmentationPerc, Pages, Records, IndexName, 
			SchemaName, TableName, AllowPageLocks, LastChecked)
	SELECT	DatabaseName, ObjectID, IndexID, PartitionNumber, FragmentationPerc, Pages, Records, IndexName, 
			SchemaName, TableName, AllowPageLocks, GETDATE()
	FROM	#IndexFrag f
	WHERE	(	SELECT	COUNT(*)
				FROM	msdb.dbo.PNGCORP_IndexList il
				WHERE	il.DatabaseName = f.DatabaseName
						AND il.ObjectID = f.ObjectID
						AND il.IndexID = f.IndexID
						AND il.PartitionNumber = f.PartitionNumber) = 0;
END
--Get the tables we need to reindex
INSERT	INTO #TableList (DatabaseName, SchemaName, TableName, Records)
SELECT	DatabaseName, SchemaName, TableName, MAX(Records)
FROM	msdb.dbo.PNGCORP_IndexList
WHERE	FragmentationPerc >= @ReorgThreshold
		AND Records >= @Rows
GROUP	BY DatabaseName, SchemaName, TableName
ORDER	BY MAX(Records) DESC
--Cycle through the problem indexes and insert them into the PNGCORP_IndexReorg# tables.
SET	@ThreadCounter = 1; 
IF @Threads > 5 SET @Threads = 5;
TRUNCATE TABLE msdb.dbo.PNGCORP_IndexReorg1;
TRUNCATE TABLE msdb.dbo.PNGCORP_IndexReorg2;
TRUNCATE TABLE msdb.dbo.PNGCORP_IndexReorg3;
TRUNCATE TABLE msdb.dbo.PNGCORP_IndexReorg4;
TRUNCATE TABLE msdb.dbo.PNGCORP_IndexReorg5;

DECLARE ReorgIndexCursor02 CURSOR FOR
SELECT	DatabaseName, SchemaName, TableName
FROM	#TableList
ORDER	BY Records DESC;

OPEN	ReorgIndexCursor02;

FETCH	NEXT FROM ReorgIndexCursor02 INTO @DatabaseName, @SchemaName, @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL = '
	INSERT	INTO msdb.dbo.PNGCORP_IndexReorg' + CAST(@ThreadCounter AS VARCHAR(1)) + ' (DatabaseName, SchemaName, TableName, IndexName, AllowPageLocks)
	SELECT	DISTINCT i.DatabaseName, i.SchemaName, i.TableName, i.IndexName, i.AllowPageLocks
	FROM	msdb.dbo.PNGCORP_IndexList i
			INNER JOIN #TableList t ON t.DatabaseName = i.DatabaseName
					AND t.SchemaName = i.SchemaName
					AND t.TableName = i.TableName
	WHERE	i.DatabaseName = ''' + @DatabaseName + '''
			AND i.SchemaName = ''' + @SchemaName + ''' 
			AND i.TableName = ''' + @TableName + '''
			AND i.FragmentationPerc >= ' + CAST(@ReorgThreshold AS VARCHAR(25)) + '
			AND i.Records >= ' + CAST(@Rows AS VARCHAR(25)) + '; ';

	EXEC (@SQL);

	SET @ThreadCounter = @ThreadCounter + 1;
	IF @ThreadCounter > @Threads SET @ThreadCounter = 1;

	FETCH	NEXT FROM ReorgIndexCursor02 INTO @DatabaseName, @SchemaName, @TableName;
END
CLOSE ReorgIndexCursor02;
DEALLOCATE ReorgIndexCursor02;

DROP TABLE #TableList;
DROP TABLE #IndexFrag;

--Start the index jobs
IF @ByPassDefrag = 0
BEGIN
	EXEC msdb.dbo.sp_start_job @Job_Name = 'Database Maintenance.IndexReorg1';
	IF @Threads >= 2 EXEC msdb.dbo.sp_start_job @Job_Name = 'Database Maintenance.IndexReorg2';
	IF @Threads >= 3 EXEC msdb.dbo.sp_start_job @Job_Name = 'Database Maintenance.IndexReorg3';
	IF @Threads >= 4 EXEC msdb.dbo.sp_start_job @Job_Name = 'Database Maintenance.IndexReorg4';
	IF @Threads = 5 EXEC msdb.dbo.sp_start_job @Job_Name = 'Database Maintenance.IndexReorg5';
END

SET NOCOUNT ON
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DatabaseName VARCHAR(255)

DECLARE CheckDatabaseIntegrityCursor CURSOR FOR
SELECT	[name]
FROM	master.sys.databases
WHERE	[name] NOT IN ('model', 'tempdb') 
ORDER	BY [name]

OPEN	CheckDatabaseIntegrityCursor

FETCH	NEXT FROM CheckDatabaseIntegrityCursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @DatabaseName + '    ---------------------------------------------------------------'
	SELECT	@SQL ='		USE [' + @DatabaseName + '] 
			
			DBCC CHECKDB(N' + CHAR(39) + @DatabaseName + CHAR(39) + ')
			
			'
			
	PRINT @SQL
	EXEC master.dbo.sp_executesql @SQL
	FETCH	NEXT FROM CheckDatabaseIntegrityCursor INTO @DatabaseName
END
CLOSE CheckDatabaseIntegrityCursor
DEALLOCATE CheckDatabaseIntegrityCursor