Index rebuilding in sql server with query

Rebuild Index with query in Sql Server

DECLARE @TableName varchar(255)DECLARE @TableSchema varchar(100)DECLARE TableCursor CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME  FROM information_schema.tablesWHERE table_type = 'base table'OPEN TableCursorFETCH NEXT FROM TableCursor INTO @TableSchema,  @TableNameWHILE @@FETCH_STATUS = 0BEGINSET @TableName=@TableSchema + '.' + @TableNameDBCC DBREINDEX(@TableName, ' ', 100)FETCH NEXT FROM TableCursor INTO @TableSchema,  @TableNameENDCLOSE TableCursorDEALLOCATE TableCursor

But if you want to learn which table needs rebuild, use this query;

DECLARE @dbid intSET @dbid = db_id()SELECT convert(decimal(5,2), avg_fragmentation_in_percent) AS avg_fragmentation_percent, object_name(d.object_id) AS [table], i.name AS [name], d.index_type_desc, alloc_unit_type_desc AS [type], index_depth, index_level, fragment_count, page_countFROM sys.dm_db_index_physical_stats( @dbid, null, -1, null, 'SAMPLED') dINNER JOIN sys.indexes i ON i.object_id=d.object_id AND i.index_id=d.index_idWHERE  avg_fragmentation_in_percent > 30;