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 '----------------------------------------------'
SELECT NAME AS IndexName
FROM SYSINDEXES
WHERE name NOT LIKE 'sys%'
ORDER BY NAME
PRINT '----------------------------------------------'
PRINT '---Re-Indexing of fragmented table Started !--'
PRINT '----------------------------------------------'
SELECT @CNT=Count(*)
FROM ##DBCCShowcontigAll
DELETE FROM ##DBCCShowcontigAll
WHERE ScanDensity=0

WHILE @CNT>0
BEGIN
IF EXISTS( SELECT 1 FROM ##DBCCShowcontigAll
WHERE ScanDensity<=@FagmentationPercentage
AND RowNum=@CNT)
BEGIN
SELECT @ObjectName=ObjectName
,@IndexName= IndexName
FROM ##DBCCShowcontigAll
WHERE ObjectName NOT LIKE 'sys%'
AND RowNum=@CNT
PRINT 'IndexName : '+@IndexName
DBCC DBREINDEX(@ObjectName ,@IndexName)
END
SET @CNT=@CNT-1
END

DROP TABLE ##DBCCShowcontigAll

EXEC sp_updatestats

PRINT '---------------------------------------------'
PRINT '-Re-Indexing of fragmented table completed !-'
PRINT '-----------------------------------------'
PRINT 'Existing Indexes after process of Re-Indexing'
PRINT '---------------------------------------------'
SELECT NAME AS IndexName
FROM SYSINDEXES
WHERE name NOT LIKE 'sys%'
ORDER BY NAME

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server