SQL Server Rebuild Index for all the tables via TSQL – 如果幫資料庫重建索引所有表單

Rebuild Index 相信是 Database Maintenance 的其中一個重要任務

如何 Rebuild All Index for All the table in a database

解決方法十分簡單.

SQL Server Management Studio (SSMS) 選擇了Database之後
建立一個新的Query

之後我們可以執行以下的 SQL程式碼

 
DECLARE @TableName varchar(255) 
 
DECLARE TableCursor CURSOR FOR 
 
SELECT table_name FROM information_schema.tables 
 
WHERE table_type = 'base table' 
 

OPEN TableCursor 
 
FETCH NEXT FROM TableCursor INTO @TableName 
 
WHILE @@FETCH_STATUS = 0 
 
BEGIN 
 
DBCC DBREINDEX(@TableName,' ',70) 
 
FETCH NEXT FROM TableCursor INTO @TableName 
 
END 
 
CLOSE TableCursor 
 
DEALLOCATE TableCursor 

我們可以參考 Technet SQL Gallery
https://gallery.technet.microsoft.com/scriptcenter/Script-for-rebuilding-all-8d079754

Hope you find it useful

Leave a Reply