SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

対象データベースの全インデックスを再構築する SQL

leave a comment

SQL Server のインデックスの再構築を定期的に実行する方法としてメンテナンスプランを使用てインデックスメンテナンスをする方法があります。

image

メンテナンスプランでは細かい制御ができない (と思っているのですが) 状況によっては T-SQL で対応する必要が出てきます。

今回の投稿では、データベース内の全ユーザーテーブルに対してインデックスの再構築をする SQL をまとめてみたいと思います。

■全インデックスを再構築する SQL


以下のような SQL で全インデックスの再構築を行うことができます。

USE [TEST]
GO
DECLARE @TableName sysname, @IndexName sysname
DECLARE @basesql nvarchar(max), @sql nvarchar(max)
DECLARE @Edition nvarchar(max)

SET @Edition = CONVERT(nvarchar, SERVERPROPERTY(‘Edition’))

SET @basesql = ‘ALTER INDEX @1 On @2 REBUILD @3’

IF PATINDEX(‘%Enterprise%’, @Edition) > 0
BEGIN
    SET @basesql = REPLACE(@basesql, ‘@3’, ‘WITH (ONLINE=ON)’)
END
ELSE
    SET @basesql = REPLACE(@basesql, ‘@3’, ”)

DECLARE IXC CURSOR FOR
SELECT
    OBJECT_NAME(object_id) AS TableName
    , name AS IndexName
FROM
    sys.indexes
WHERE
    OBJECT_SCHEMA_NAME (object_id) <> ‘sys’
    AND
    index_id > 0
ORDER BY 1

OPEN IXC

FETCH NEXT FROM IXC
INTO @TableName, @IndexName

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @TableName + ‘:’ + @IndexName

    SET @sql = REPLACE(@basesql, ‘@1’, @IndexName)
    SET @sql = REPLACE(@sql, ‘@2’, @TableName)
   
    EXECUTE (@sql)

    FETCH NEXT FROM IXC
    INTO @TableName, @IndexName   
END

CLOSE IXC
DEALLOCATE IXC

 

SQL Server ではエディションによって、使用できる機能に差がありますので、エディションを判断してオプションを書き換えるようにしています。
# Standard Edition での動作確認ができていないのですが…。

T-SQL で動的な文字列を生成してインデックスの再構築を行っていますので、柔軟に設定を変更することが可能です。

この手の保守用のスクリプトは一度作成すると使いまわしができますのでいろいろとストックしておくと便利ですね。

Written by masayuki.ozawa

11月 4th, 2012 at 9:15 pm

Posted in SQL Server

Tagged with

Leave a Reply

*