ms sql server 重建或重组数据库索引

Create procedure Upc_IndexDefrag
as
Begin
	Declare @DBName NVARCHAR(255)
			,@Tablename NVARCHAR(255)
			,@Schemaname NVARCHAR(255)
			,@iNDEXnAME NVARCHAR(255)
			,@PctFrag DECIMAL
	Declare @DeFrag NVARCHAR(MAX)
	IF(EXISTS(SELECT 1 FROM sys.objects where object_id = OBJECT_ID(N'#Frag')))		
	BEGIN
		DROP TABLE #Frag
	END
	CREATE TABLE #Frag
	(
		DBName	NVARCHAR(255)
		,TableName	NVARCHAR(255)
		,SchemaName NVARCHAR(255)
		,IndexName NVARCHAR(255)
		,AvgFragment DECIMAL
	)
	EXEC sp_MSforeachdb N'INSERT INTO #Frag(
		DBName,
		TableName,
		Schemaname,
		IndexName,
		AvgFragment
		)SELECT ''?'' AS DBName
			,t.Name as TableName
			,sc.Name AS Schemaname
			,I.NAME AS IndexName
			,s.avg_fragmentation_in_percent
	FROM ?.sys.dm_db_index_physical_stats(DB_ID(''?''),NULL,NULL,NULL,''Sampled'') AS s
		JOIN ?.sys.indexes i
		ON s.Object_id = i.Object_id
			AND s.Index_id = i.Index_id
		JOIN ?.sys.tables t
		ON i.Object_id = t.Object_id
		Join ?.sys.schemas sc
		ON t.schema_id = sc.schema_id
	WHERE s.avg_fragmentation_in_Percent > 20
	AND t.TYPE = ''U''	
	AND s.page_count > 8
	ORDER BY TableName,IndexName'
	DECLARE cList CURSOR
	FOR SELECT * FROM #Frag
	OPEN cList
	FETCH NEXT FROM cList
	INTO @DBName,@TableName,@SchemaName,@IndexName,@PctFrag
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF(@PctFrag between 20.0 AND 40.0)
		BEGIN
			SET @DeFrag = N'ALTER INDEX '+@iNDEXnAME + N' ON '+ @DBName+N'.'+@Schemaname+N'.' + @Tablename + N' REORGANIZE'
			EXEC sp_executesql @DeFrag
			--print @DeFrag
			Print N'REORGANIZE index:'+@DBName+N'.'+ @Schemaname+N'.'+@Tablename+N'.'+@iNDEXnAME
		END
		ELSE IF @PctFrag > 40.0
		BEGIN
			SET @DeFrag = N'ALTER INDEX ' + @iNDEXnAME +N' ON '+@DBName+N'.'+ @Schemaname+N'.'+@Tablename+N' REBUILD'
			EXEC sp_executesql @DeFrag
			Print N'Rebuild index:'+@DBName+N'.'+ @Schemaname+N'.'+@Tablename+N'.'+@iNDEXnAME
		END
		FETCH NEXT FROM cList
		INTO @DBName,@TableName,@SchemaName,@IndexName,@PctFrag
	END
	CLOSE cList
	DEALLOCATE cList
	DROP TABLE #Frag
End