Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*Perform a 'USE <database name>' to select the database in which to run the script.*/
- -- Declare variables
- SET NOCOUNT ON;
- DECLARE @tablename VARCHAR(255);
- DECLARE @execstr VARCHAR(400);
- DECLARE @objectid INT;
- DECLARE @indexid INT;
- DECLARE @frag DECIMAL;
- DECLARE @maxfrag DECIMAL;
- -- Decide on the maximum fragmentation to allow for.
- SELECT @maxfrag = 10.0;
- -- Declare a cursor.
- DECLARE TABLES CURSOR FOR
- SELECT TABLE_SCHEMA + '.' + TABLE_NAME
- FROM INFORMATION_SCHEMA.TABLES
- WHERE TABLE_TYPE = 'BASE TABLE';
- -- Create the table.
- CREATE TABLE #fraglist (
- ObjectName CHAR(255),
- ObjectId INT,
- IndexName CHAR(255),
- IndexId INT,
- Lvl INT,
- CountPages INT,
- CountRows INT,
- MinRecSize INT,
- MaxRecSize INT,
- AvgRecSize INT,
- ForRecCount INT,
- Extents INT,
- ExtentSwitches INT,
- AvgFreeBytes INT,
- AvgPageDensity INT,
- ScanDensity DECIMAL,
- BestCount INT,
- ActualCount INT,
- LogicalFrag DECIMAL,
- ExtentFrag DECIMAL);
- -- Open the cursor.
- OPEN TABLES;
- -- Loop through all the tables in the database.
- FETCH NEXT
- FROM TABLES
- INTO @tablename;
- WHILE @@FETCH_STATUS = 0
- BEGIN
- -- Do the showcontig of all indexes of the table
- INSERT INTO #fraglist
- EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
- WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
- FETCH NEXT
- FROM TABLES
- INTO @tablename;
- END;
- -- Close and deallocate the cursor.
- CLOSE TABLES;
- DEALLOCATE TABLES;
- -- Declare the cursor for the list of indexes to be defragged.
- DECLARE indexes CURSOR FOR
- SELECT ObjectName, ObjectId, IndexId, LogicalFrag
- FROM #fraglist
- WHERE LogicalFrag >= @maxfrag
- AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
- -- Open the cursor.
- OPEN indexes;
- -- Loop through the indexes.
- FETCH NEXT
- FROM indexes
- INTO @tablename, @objectid, @indexid, @frag;
- WHILE @@FETCH_STATUS = 0
- BEGIN
- PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',' + RTRIM(@indexid) + ') - fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%';
- SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ', ' + RTRIM(@indexid) + ')';
- EXEC (@execstr);
- FETCH NEXT
- FROM indexes
- INTO @tablename, @objectid, @indexid, @frag;
- END;
- -- Close and deallocate the cursor.
- CLOSE indexes;
- DEALLOCATE indexes;
- -- Delete the temporary table.
- DROP TABLE #fraglist;
- GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement