sqlserver index rebuild

Below is the script to identify the fragmentation report for particular database.

USE DatabaseName -- use the database name here

GO

SELECT object_name(IPS.object_id) AS [TableName],

   SI.name AS [IndexName],

   IPS.Index_type_desc,

   IPS.avg_fragmentation_in_percent,

   IPS.avg_fragment_size_in_pages,

   IPS.avg_page_space_used_in_percent,

   IPS.record_count,

   IPS.ghost_record_count,

   IPS.fragment_count,

   IPS.avg_fragment_size_in_pages

FROM sys.dm_db_index_physical_stats(db_id(N'ManageSoft'), NULL, NULL, NULL , 'DETAILED') IPS

   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id

   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id

WHERE ST.is_ms_shipped = 0

ORDER BY 1,5

GO

The above script is only to know the fragmentation status, if you do not want the fragmentation report you ignore the above script.

Directly run the below script to rebuild the indexes of the particular database.

USE DatabaseName --Enter the name of the database you want to reindex

 DECLARE @TableName varchar(255)

 DECLARE TableCursor CURSOR FOR

SELECT table_name FROM information_schema.tables

WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

DBCC DBREINDEX(@TableName,' ',90)

FETCH NEXT FROM TableCursor INTO @TableName

END