Query For Re-indexing and gather Statistics On Database
USE DatabaseName GO --Declaration of local variables Declare @CNT int , @ObjectName sysname , @IndexName sysname , @FagmentationPercentage int SET @FagmentationPercentage = 90 IF Object_id('##DBCCShowcontigAll') is not Null BEGIN DROP TABLE ##DBCCShowcontigAll End CREATE TABLE ##DBCCShowcontigAll ( RowNum Int Identity , ObjectName sysname , ObjectId int , IndexName sysname , IndexId smallint , Levle smallint , Pages int , TRows int , MinimumRecordSize int , MaximumRecordSize Int , AverageRecordSize decimal(9,5) , ForwardedRecords int , Extents int , ExtentSwitches int , AverageFreeBytes decimal(9,5) , AveragePageDensity float , ScanDensity float , BestCount int , ActualCount int , LogicalFragmentation float , ExtentFragmentation Float ) INSERT INTO ##DBCCShowcontigAll EXEC ('DBCC SHOWCONTIG WITH ALL_INDEXES ,TABLERESULTS ') PRINT 'Existing Indexes before process of Re-Indexing' PRINT