SQL Study
今天看了本SQL電子書,介紹閞於索引的維護及管理,
裡面有用到一個系統的StoredProcedure,
叫做sp_MSforeachtable,做為Rebuild Indexes,
官方的公開文件似乎沒有提到,
使用過後覺得還蠻不錯用了,所以筆記下來~
因介紹中有分SQL 2000及SQL 2005/2008,
故便將兩段語法都貼上,以免日後忘了~
SQL Server 2000
--Rebuild all indexes with keeping the default fill factor for each index
USE [DATABASE_NAME];
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')";
GO
--Rebuild all indexes with specifying the fill factor
USE [DATABASE_NAME];
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ',
[FILL_FACTOR_PERC])";
GO
SQL Server 2005/2008
--Rebuild all indexes online with keeping the default fill factor for each index
USE [DATABASE_NAME];
GO
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ?
REBUILD WITH (ONLINE=ON)";
GO
--Rebuild all indexes offline with keeping the default fill factor for each index
USE [DATABASE_NAME];
GO
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ?
REBUILD WITH (ONLINE=OFF)";
GO
--Rebuild all indexes online with specifying the fill factor
USE [DATABASE_NAME];
GO
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ?
REBUILD WITH (FILLFACTOR=[FILL_FACTOR_PERC],ONLINE=ON)";
GO
--Rebuild all indexes offline with specifying the fill factor
USE [DATABASE_NAME];
GO
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ?
REBUILD WITH (FILLFACTOR=[FILL_FACTOR_PERC],ONLINE=OFF)";
GO
