Get the per-object last allocated page in files and generate alter index rebuild to move it to the beginning

check-last-page-by-object-and-rebuild.sql

-- Notes
--
-- 1) you may find that the allocated pages toward the end of files belong to
-- one or more system objects, in which case you can't rebuild/reorg them, so you
-- may need to shrink the files to move at least these and then resume from the next
-- non system object
--
-- 2) rebuilding heaps is not implemented, it's just a matter to add a CASE, I'll do it
-- the first time I have to so that I can test it
--
-- 3) remember rebuild requires roughly 1.2 times the size of the index (it depends,
-- I knwow, whatever...) so REORGANIZE may be a better strategy in some cases
-- (space wise, but remember you will be introducing fragmentation)

WITH cte AS (
	SELECT	ROW_NUMBER()
			OVER (PARTITION BY object_id, index_id, partition_id
				ORDER BY allocated_page_page_id DESC
			) AS rn, *
	FROM	sys.dm_db_database_page_allocations(DB_ID(), null, null, null, 'limited')
	)
SELECT
	OBJECT_NAME(P.object_id) AS object_name
,	I.name AS index_name
,	P.partition_id
,	P.allocated_page_page_id
,	F.[name] AS file_name
,	CASE
	WHEN	O.is_ms_shipped = 1
	THEN	N'System object, rebuild/reorg not supported!'
	ELSE	'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' + QUOTENAME(OBJECT_NAME(P.object_id)) + ' REBUILD'
	END AS reorgsqlstmt
FROM	cte AS P
JOIN	sys.all_objects AS O
  ON	P.object_id = O.object_id
JOIN	sys.indexes AS I
  ON	P.object_id = I.object_id
 AND	P.index_id = I.index_id
JOIN	sys.database_files AS F
  ON	P.allocated_page_file_id = F.file_id
WHERE	P.rn = 1
ORDER BY P.allocated_page_page_id DESC;