SQL Server 索引维护sql语句

感兴趣的小伙伴，下面一起跟随编程之家 52php.cn的小编两巴掌来看看吧！

代码如下:

DBCC SHOWCONTIG WITH FAST,TABLERESULTS,ALL_INDEXES,NO_INFOMSGS 

代码如下:

/*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 @IndexName varchar(500); 
DECLARE @indexid int; 
DECLARE @frag decimal; 
DECLARE @maxfrag decimal; 
DECLARE @TmpName varchar(500); 

-- Declare @TmpName ='' 
set @TmpName = '' 

-- Decide on the maximum fragmentation to allow for. 
SELECT @maxfrag = 30.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,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,IndexName,IndexId,LogicalFrag 
FROM #fraglist 
WHERE INDEXPROPERTY (ObjectId,'IndexDepth') > 0; 

-- Open the cursor. 
OPEN indexes; 

-- Loop through the indexes. 
FETCH NEXT 
FROM indexes 
INTO @tablename,@objectid,@IndexName,@indexid,@frag; 


WHILE @@FETCH_STATUS = 0 
BEGIN; 
if @frag < @maxfrag 
Begin 
SELECT @execstr = 'ALTER INDEX [' + RTRIM(@IndexName) + '] ON [' + RTRIM(@tablename) + '] REORGANIZE WITH ( LOB_COMPACTION = ON ) ' 
print @maxfrag + ' ' + @execstr 
End 
else 
Begin 
SELECT @execstr = 'ALTER INDEX [' + RTRIM(@IndexName) + '] ON [' + RTRIM(@tablename) + '] REBUILD WITH ( PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON,SORT_IN_TEMPDB = OFF,ONLINE = OFF )' 
print @maxfrag + ' ' + @execstr 
End 

EXEC (@execstr); 

--更新统计信息 
IF @TmpName<>@tablename 
BEGIN 
SET @tmpName=@tableName 
PRINT 'UPDATE STATISTICS '+@TableName + ' WITH FULLSCAN ' 
EXEC ('UPDATE STATISTICS '+@TableName + ' WITH FULLSCAN ') 
END 


FETCH NEXT 
FROM indexes 
INTO @tablename,@frag; 
END; 

-- Close and deallocate the cursor. 
CLOSE indexes; 
DEALLOCATE indexes; 

-- Delete the temporary table. 
DROP TABLE #fraglist; 
GO 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!