Online rebuild all tables and indexes (partition and lob aware)

online-rebuild-all-tables-indexes.sql

SELECT
	CASE
	WHEN I1.[type_desc] IN ('HEAP', 'CLUSTERED')
	THEN 'ALTER TABLE [' + S1.[name] + '].[' + O1.[name] + ']'
	WHEN I1.[type_desc] = 'NONCLUSTERED'
	THEN 'ALTER INDEX [' + I1.[name] + '] ON [' + S1.[name] + '].[' + O1.[name] + ']'
	END
	+ ' REBUILD' +
	CASE
	WHEN EXISTS (SELECT * FROM sys.partitions AS P2 WHERE P1.[object_id] = P2.[object_id] AND P1.[index_id] = P2.[index_id] AND P2.partition_number > 1)
	THEN ' PARTITION = ' + CAST(P1.partition_number AS varchar)
	ELSE ''
	END
	+ ' WITH (ONLINE = ' +
	CASE
	WHEN	(I1.[type_desc] IN ('HEAP', 'CLUSTERED')
			AND EXISTS (SELECT * FROM sys.columns AS C1 JOIN sys.types AS T1 ON C1.system_type_id = T1.system_type_id AND (T1.max_length = -1 OR T1.[name] IN ('text', 'ntext', 'image')) WHERE O1.[object_id] = C1.[object_id]))
	     OR (I1.[type_desc] = 'NONCLUSTERED'
			AND EXISTS (SELECT * FROM sys.index_columns AS C1 JOIN sys.columns AS C2 ON C1.[object_id] = C2.[object_id] AND C1.column_id = C2.column_id JOIN sys.types AS T1 ON C2.system_type_id = T1.system_type_id AND (T1.max_length = -1 or T1.[name] in ('text', 'ntext', 'image')) WHERE I1.[object_id] = C1.[object_id] AND I1.index_id = C1.index_id))
	THEN 'OFF'
	ELSE 'ON'
	END
	+ ', DATA_COMPRESSION = PAGE'
	+ ');'
	+ ' -- ' + CAST(P1.[rows] AS varchar) + ' rows'
	AS RebuildSQLStmt
--,	I1.*, O1.*, P1.*
FROM	sys.indexes AS I1
JOIN	sys.objects AS O1
  ON	I1.[object_id] = O1.[object_id]
JOIN	sys.schemas AS S1
  ON	O1.[schema_id] = S1.[schema_id]
JOIN	sys.partitions AS P1
  ON	I1.[object_id] = P1.[object_id]
 AND	I1.[index_id] = P1.[index_id]
WHERE	O1.is_ms_shipped = 0
  AND	P1.[rows] > 0
ORDER BY
		P1.[rows]

online-rebuild-heaps-reorg-others.sql

SELECT
	CASE
	WHEN I1.[type_desc] = ('HEAP')
	THEN 'ALTER TABLE [' + S1.[name] + '].[' + O1.[name] + '] REBUILD' 
	WHEN I1.[type_desc] IN ('NONCLUSTERED', 'CLUSTERED')
	THEN 'ALTER INDEX [' + I1.[name] + '] ON [' + S1.[name] + '].[' + O1.[name] + '] REORGANIZE'
	END
	+
	CASE
	WHEN EXISTS (SELECT * FROM sys.partitions AS P2 WHERE P1.[object_id] = P2.[object_id] AND P1.[index_id] = P2.[index_id] AND P2.partition_number > 1)
	THEN ' PARTITION = ' + CAST(P1.partition_number AS varchar)
	ELSE ''
	END
	+ ' WITH ('
	+	CASE
		WHEN I1.[type_desc] IN ('NONCLUSTERED', 'CLUSTERED')
		THEN 'LOB_COMPACTION = ON'
		WHEN I1.[type_desc] = ('HEAP') 
		THEN 'ONLINE = '
		+	CASE
				WHEN EXISTS (SELECT * FROM sys.columns AS C1 JOIN sys.types AS T1 ON C1.system_type_id = T1.system_type_id AND (T1.max_length = -1 OR T1.[name] IN ('text', 'ntext', 'image')) WHERE O1.[object_id] = C1.[object_id])
				THEN 'OFF'
				ELSE 'ON'
			END
		END
	+ ');'
	+ ' -- ' + CAST(P1.[rows] AS varchar) + ' rows'
	AS RebuildSQLStmt
--,	I1.*, O1.*, P1.*
FROM	sys.indexes AS I1
JOIN	sys.objects AS O1
  ON	I1.[object_id] = O1.[object_id]
JOIN	sys.schemas AS S1
  ON	O1.[schema_id] = S1.[schema_id]
JOIN	sys.partitions AS P1
  ON	I1.[object_id] = P1.[object_id]
 AND	I1.[index_id] = P1.[index_id]
WHERE	O1.is_ms_shipped = 0
  AND	P1.[rows] > 0
ORDER BY
		P1.[rows]

online-reorg-offline-reuilds-only.sql

SELECT
	'ALTER INDEX [' + I1.[name] + '] ON [' + S1.[name] + '].[' + O1.[name] + ']'
	+ ' REORGANIZE' +
	CASE
	WHEN EXISTS (SELECT * FROM sys.partitions AS P2 WHERE P1.[object_id] = P2.[object_id] AND P1.[index_id] = P2.[index_id] AND P2.partition_number > 1)
	THEN ' PARTITION = ' + CAST(P1.partition_number AS varchar)
	ELSE ''
	END
	+ ' WITH (LOB_COMPACTION = ON);'
	+ ' -- ' + CAST(P1.[rows] AS varchar) + ' rows'
	AS ReorganizeSQLStmt
--,	I1.*, O1.*, P1.*
FROM	sys.indexes AS I1
JOIN	sys.objects AS O1
  ON	I1.[object_id] = O1.[object_id]
JOIN	sys.schemas AS S1
  ON	O1.[schema_id] = S1.[schema_id]
JOIN	sys.partitions AS P1
  ON	I1.[object_id] = P1.[object_id]
 AND	I1.[index_id] = P1.[index_id]
WHERE	O1.is_ms_shipped = 0
  AND	P1.[rows] > 0
  AND	((I1.[type_desc] = 'CLUSTERED'
			AND EXISTS (SELECT * FROM sys.columns AS C1 JOIN sys.types AS T1 ON C1.system_type_id = T1.system_type_id AND (T1.max_length = -1 OR T1.[name] IN ('text', 'ntext', 'image')) WHERE O1.[object_id] = C1.[object_id]))
	     OR (I1.[type_desc] = 'NONCLUSTERED'
			AND EXISTS (SELECT * FROM sys.index_columns AS C1 JOIN sys.columns AS C2 ON C1.[object_id] = C2.[object_id] AND C1.column_id = C2.column_id JOIN sys.types AS T1 ON C2.system_type_id = T1.system_type_id AND (T1.max_length = -1 or T1.[name] in ('text', 'ntext', 'image')) WHERE I1.[object_id] = C1.[object_id] AND I1.index_id = C1.index_id))
		)
ORDER BY
		P1.[rows]