SQL SERVER: Find primary keys where the first column in the PK is an identity and the index has a FILL FACTOR adding unneeded empty space

Find PK Indexes With Identitys with Fill Factor.sql

-- FIND ALL PRIMARY KEYS WHERE THE FIRST COLUMN IS AN IDENTITY AND THE FILL FACTOR ADDS EMPTY SPACE

SELECT [db_name] = DB_NAME(),
	[schema_name] = OBJECT_SCHEMA_NAME(i.[object_id], DB_ID()),
	[table_name] = OBJECT_NAME(i.[object_id]),  
	[index_name] = [i].Name, 
	[i].[type_desc], 
	[i].[fill_factor], 
	[index_in_row_size_in_mb] = CAST(fn1.[IndexInRowSizeInMB] AS DECIMAL(19,4)),
	[index_in_row_empty_mb] = CAST(fn2.[IndexInRowEmptyMB] AS DECIMAL(19,4)),
	[index_lob_size_in_mb] = CAST(fn1.[IndexLOBSizeInMB] AS DECIMAL(19,4)),
	ddps.[row_count],
	CONCAT('ALTER INDEX ', i.[name], ' ON ', 
		QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id], DB_ID())), '.', 
		QUOTENAME(OBJECT_NAME(i.[object_id])), 
		' REBUILD WITH (FILLFACTOR = 100', 
		CASE WHEN SERVERPROPERTY('EngineEdition') = 3 THEN ', ONLINE = ON);' ELSE '' END, ');') AS fix_script
FROM sys.[indexes] i 
INNER JOIN sys.[dm_db_partition_stats] ddps 
	ON [ddps].[index_id] = [i].[index_id] 
	AND [ddps].[object_id] = [i].[object_id] 
INNER JOIN sys.[index_columns] ic 
	ON [i].[object_id] = [ic].[object_id] 
	AND [i].[index_id] = [ic].[index_id] 
	AND [ic].[key_ordinal] = 1
INNER JOIN sys.[columns] c 
	ON [i].[object_id] = [c].[object_id]  
	AND [ic].[column_id] = [c].[column_id]
CROSS APPLY (
	SELECT [IndexInRowSizeInMB] = ddps.[in_row_data_page_count] / 128.0, 
		[IndexLOBSizeInMB] = ddps.[lob_used_page_count] / 128.0
) fn1
CROSS APPLY (
	SELECT [IndexInRowEmptyMB] = fn1.[IndexInRowSizeInMB] * (1 - (i.[fill_factor] / 100.0))
) fn2
WHERE [i].[type] = 1 -- clustered
	AND [ic].[key_ordinal] = 1 -- first column in pk
	AND [c].[is_identity]  = 1 
	AND ([i].[fill_factor] > 0 AND [i].[fill_factor] < 100)
ORDER BY [schema_name], 
	[table_name]