Index rebuild online

  

DECLARE @page_count_minimum smallint     

DECLARE @fragmentation_minimum float

SET @page_count_minimum   =10

SET @fragmentation_minimum   = 10

SELECT

'ALTER INDEX ' + ' ['  +sys.indexes.name  + '] ' + 'On'+ ' ' + sys.objects.name + ' REBUILD  WITH (ONLINE = ON)'   as   b,sys.objects.name,

avg_fragmentation_in_percent AS frag,

page_count AS page_count,

sys.dm_db_index_physical_stats.object_id AS objectid, 

partition_number AS partitionnum

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

inner join sys.objects 

on sys.objects.object_id = sys.dm_db_index_physical_stats.object_id

inner join sys.indexes 

on sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id 

and sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id

WHERE avg_fragmentation_in_percent > @fragmentation_minimum 

AND sys.dm_db_index_physical_stats.index_id > 0 

AND page_count > @page_count_minimum

ORDER BY  2  

go

 

-- can filter top 100 or select only table names by joining sysojbects and  can also get schema name by joining with schemas

 

select s.name as schema_name, s.schema_id, u.name as schema_owner from sys.schemas s inner join sys.sysusers u on u.uid = s.principal_id order by s.name