『依頼された事をブログに書いてみる -その1- 』が適当だったので、きちんと SQL Server 2008 R2 の環境を準備して検証してみた - 都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

　ちょっと以下の記事内容が適当すぎなので、
ryuchan.hatenablog.com

　SQL Server 2008 R2 の環境を準備して検証しました。懐かしいなぁ...はじめて SQL Server を扱ったのは、2008でした。

f:id:koogucc11:20151210185557p:plain
　
　結構間違いがありましたwww　修正したクエリは下記の通りです。

DECLARE @schema_name     NVARCHAR(500)
DECLARE @table_name      NVARCHAR(500)
DECLARE @index_name      NVARCHAR(500)
DECLARE @count1          INT
DECLARE @count2          INT
DECLARE @msg             NVARCHAR(500)
DECLARE @ddlstatement    NVARCHAR(500)

DECLARE REBUILD_INDEX_LIST CURSOR FOR
SELECT
    s.name,
    t.name,
    i.name,
    count1,
    count2
FROM
    sys.tables t
    INNER JOIN
        sys.schemas s ON
        t.schema_id = s.schema_id
    INNER JOIN
        sys.indexes i ON
        t.object_id = i.object_id
CROSS APPLY
(
    SELECT
        COUNT(c.name)
    FROM
        sys.index_columns ic
        INNER JOIN 
            sys.columns c ON
            ic.object_id = c.object_id AND
            ic.column_id = c.column_id
        INNER JOIN sys.types ty ON
            c.user_type_id = ty.user_type_id
    WHERE
        i.object_id = ic.object_id AND
        i.index_id = ic.index_id AND
        (ty.name IN ('image','text','ntext') OR c.max_length = -1)
) AS ca1(count1)
CROSS APPLY
(
    SELECT 
        COUNT(c.name)
    FROM 
        sys.columns c
        INNER JOIN sys.types ty ON
            c.user_type_id = ty.user_type_id
    WHERE
        c.object_id = t.object_id AND
        (ty.name IN ('image','text','ntext') OR c.max_length = -1) AND
        i.type_desc = 'CLUSTERED'
) AS ca2(count2) 
WHERE 
    NOT EXISTS (SELECT 
                    object_id 
                FROM
                    sys.fulltext_indexes 
                WHERE 
                    object_id = i.object_id AND 
                    unique_index_id = i.index_id)  AND
    i.name IS NOT NULL AND
    i.type_desc IN ('CLUSTERED','NONCLUSTERED') AND 
    t.is_ms_shipped = 0
ORDER BY
    s.name,
    t.name,
    i.index_id
OPEN REBUILD_INDEX_LIST
FETCH NEXT FROM REBUILD_INDEX_LIST into @schema_name,
                                        @table_name,
                                        @index_name,
                                        @count1,
                                        @count2
WHILE @@FETCH_STATUS =0 
BEGIN
    IF @count1 > 0 OR @count2 > 0
        BEGIN
            SET @ddlstatement = 'ALTER INDEX '+ @index_name + ' ON ' + @schema_name + '.' + @table_name + 
                                ' REBUILD PARTITION = ALL WITH (ONLINE = OFF, DATA_COMPRESSION = PAGE, SORT_IN_TEMPDB = ON )'
        END
    ELSE
        BEGIN
            SET @ddlstatement = 'ALTER INDEX '+ @index_name + ' ON ' + @schema_name + '.' + @table_name + 
                                ' REBUILD PARTITION = ALL WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, SORT_IN_TEMPDB = ON )'
        END
    PRINT @ddlstatement
    PRINT 'GO'
    FETCH NEXT FROM REBUILD_INDEX_LIST into @schema_name,
                                            @table_name,
                                            @index_name,
                                            @count1,
                                            @count2
END

CLOSE REBUILD_INDEX_LIST
DEALLOCATE REBUILD_INDEX_LIST