SQL-Server-File-Groups/Move-Data-Between-Filegroups/sp_MoveTablesToFileGroup.sql - master - ypermitin/SQLServerTools

SQL-Server-File-Groups/Move-Data-Between-Filegroups/sp_MoveTablesToFileGroup.sql
206 строк14 KB
Permitin Yury
Create sp_MoveTablesToFileGroup.sql
05 апр 2019, 23:49
05 апр 2019, 23:49044e2cd
use master;
GO
------------------------------------------------------------------------------------------
--  sp_MoveTablesToFileGroup
--
--  Moves tables, heaps and indexes and LOBS to a filegroup.
--  Author:   Mark White   (maranite@gmail.com)
------------------------------------------------------------------------------------------
CREATE PROC [dbo].[sp_MoveTablesToFileGroup]
    @SchemaFilter        varchar(255)   = '%',          -- Filter which table schemas to work on
    @TableFilter         varchar(255)   = '%',          -- Filter which tables to work on
    @DataFileGroup       varchar(255)   = 'PRIMARY',    -- Name of filegroup that data must be moved to
    @LobFileGroup        varchar(255)   = NULL,         -- Name of filegroup that LOBs (if any) must be moved to
    @FromFileGroup       varchar(255)   = '%',          -- Only move objects that currenly occupy this filegroup
    @ClusteredIndexes    bit            = 1,            -- 1 = move clustered indexes (table data), else 0
    @SecondaryIndexes    bit            = 1,            -- 1 = move secondary indexes, else 0
    @Heaps               bit            = 1,            -- 1 = move heaps (lazy-assed, unindexed crap), else 0
    @Online              bit            = 0,            -- 1 = keep indexes online (required Enterprise edition)
    @ProduceScript       bit            = 0             -- 1 = emit a T-SQL script instead of performing the moves
AS
BEGIN
    SET NOCOUNT ON;
    SET CONCAT_NULL_YIELDS_NULL ON;

    IF FILEGROUP_ID(@DataFileGroup) IS NULL 
        RAISERROR('Invalid Data FileGroup specified.',10,1);
    
    IF @Online = 1 AND SERVERPROPERTY('EngineEdition') <> 3 
        RAISERROR('SQL Server Enterprise edition is required for online index operations.',10,1);

    IF (@LobFileGroup IS NOT NULL) AND (@@MICROSOFTVERSION / 0x01000000) < 11
        RAISERROR('LOB data can only be moved in SQL 2012 or newer. Consider re-creating your table/s.',10,1);

    DECLARE @SQL VARCHAR(MAX) = '';
    DECLARE @Script VARCHAR(MAX) = '';
	DECLARE @RANDOM_NAME VARCHAR(100) = REPLACE(NEWID(),'-','');

    DECLARE C CURSOR FOR 
        WITH TYPED_COLUMNS AS (
            SELECT name = '[' + col.name + '] ', col.is_nullable, col.user_type_id, col.max_length, col.object_id, col.column_id, 
                  [type_name] = '[' + typ.name + '] '
            from sys.columns col
            JOIN sys.types typ on typ.user_type_id = col.user_type_id
        ),   
        INDEX_COLUMNS AS (
            SELECT col.*, k.index_id, k.is_included_column, k.key_ordinal, k.is_descending_key
            from TYPED_COLUMNS col
            join sys.index_columns as k on k.object_id = col.object_id and k.column_id = col.column_id
        )
        SELECT DISTINCT
        /*  If the table contains LOB data which does not reside where the caller would like it to reside, then use the Brad Hoff's neat 
            partition scheme trick to move LOB data. Effectively, we simply create a partition function & scheme, rebuild the index on that
			scheme, and then allow the normal rebuild (without partitioning) to be done afterwards.
            For details, see Kimberly Tripp's site: http://www.sqlskills.com/blogs/kimberly/understanding-lob-data-20082008r2-2012/)         
	    */
        CASE WHEN COALESCE([lob_fg], @LobFileGroup,'PRIMARY') <> COALESCE(@LobFileGroup, [lob_fg], 'PRIMARY') AND [first_ix_col_type] IS NOT NULL AND [type_desc] <> 'NONCLUSTERED'
        THEN            
            'CREATE PARTITION FUNCTION PF_' + random_name + ' (' + [first_ix_col_type] + ') AS RANGE RIGHT FOR VALUES (0);' + CHAR(13) + 
            'CREATE PARTITION SCHEME PS_' + random_name + ' AS PARTITION PF_' + random_name + ' TO ([' + @LobFileGroup + '],[' + @LobFileGroup + ']);' + CHAR(13) + CHAR(13) +
            CASE [type_desc]
				WHEN 'HEAP' 
				THEN 'CREATE CLUSTERED ' + index_on_table + ' (' + index_columns + ') ' + options + ' ON PS_' + random_name + '(' + [first_ix_col_name] + ');' + CHAR(13) 
					+ 'DROP ' + index_on_table + ';' + CHAR(13) 
        
				ELSE 'CREATE ' + is_unique + [type_desc] + ' '
							   + index_on_table + ' (' + index_columns + ')' + CHAR(13) + [includes / filters] + options +  'ON PS_' + random_name + '(' + [first_ix_col_name] + ');' + CHAR(13) 
				END 
				+ CHAR(13)
			ELSE '' END 
        + 
        CASE [type_desc]
            WHEN 'HEAP' 
            THEN 'CREATE CLUSTERED ' + index_on_table + ' (' + index_columns + ') ' + options + ' ON [' + @DataFileGroup + '];' + CHAR(13) 
               + 'DROP ' + index_on_table + ';' + CHAR(13)       
        
            ELSE 'CREATE ' + is_unique + [type_desc] + ' '
									   + index_on_table + ' (' + index_columns + ')' + CHAR(13) + [includes / filters] + options +  'ON [' + @DataFileGroup + '];'
        END
        + 
        CASE WHEN COALESCE([lob_fg], @LobFileGroup,'PRIMARY') <> COALESCE(@LobFileGroup, [lob_fg], 'PRIMARY')   AND [first_ix_col_type] IS NOT NULL
        THEN            
            CHAR(13) + CHAR(13) + 
            'DROP PARTITION SCHEME PS_' + random_name + ';' + CHAR(13) +
            'DROP PARTITION FUNCTION PF_' + random_name + ';' + CHAR(13) + CHAR(13) 
        ELSE '' END 
        FROM 
        (
            select distinct 
					index_on_table       =   'INDEX [' + ISNULL(i.name, 'PK_' + sch.name + '_' +  obj.name) COLLATE DATABASE_DEFAULT + ']' + CHAR(13) + 'ON [' + sch.name + '].[' + obj.name + ']',
                    type_desc            =   i.type_desc,
                    is_unique            =   CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END ,                                
                    [lob_fg]             =   CASE WHEN (i.type IN (0,1) AND EXISTS (SELECT * FROM TYPED_COLUMNS col WHERE col.object_id = obj.object_id and col.max_length = -1)) 
                                                    OR (i.type = 2 AND EXISTS (SELECT * FROM INDEX_COLUMNS col WHERE col.object_id = i.object_id and col.index_id = i.index_id AND col.max_length = -1)) 
                                                THEN 
                                                    FILEGROUP_NAME(obj.lob_data_space_id)
                                                END,
                    [index_columns]         =   REPLACE(
                                                    ISNULL(
                                                        (SELECT col.name + CASE WHEN is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END as [data()]
                                                        FROM INDEX_COLUMNS col 
                                                        where col.object_id = i.object_id and col.index_id = i.index_id and col.is_included_column <> 1 
                                                        and i.type in (1,2)
                                                        order by key_ordinal
                                                        for xml path(''))
                                                        ,
                                                        (select TOP 1 '[' + col.name + '] ' as [data()]
                                                        from sys.columns col  
                                                        where col.object_id = i.object_id and i.type = 0 and (col.user_type_id in (48,52,56,58,59,62,104,127,106,108) or col.max_length between 1 and 800)
                                                        order by col.is_nullable desc
                                                        for xml path(''))
                                                     )
                                                , ' ['   
                                                , ', ['
                                                ),
                    [first_ix_col_name]         =   ISNULL(
                                                        (SELECT TOP 1 col.name
                                                        FROM INDEX_COLUMNS col 
                                                        WHERE col.object_id = i.object_id and col.index_id = i.index_id 
                                                        and col.is_included_column <> 1 and i.type in (1,2)
                                                        order by key_ordinal)
                                                        ,
                                                        (select TOP 1 name --type_name 
                                                        from TYPED_COLUMNS col 
                                                        where col.object_id = i.object_id and i.type = 0 and (col.user_type_id in (48,52,56,58,59,62,104,127,106,108) or col.max_length between 1 and 800)
                                                        order by col.is_nullable desc)
                                                    ),
                    [first_ix_col_type]      =      ISNULL(
                                                        (select TOP 1 type_name
                                                        FROM INDEX_COLUMNS col 
                                                        JOIN sys.types typ on typ.user_type_id = col.user_type_id
                                                        WHERE col.object_id = i.object_id and col.index_id = i.index_id 
                                                        and col.is_included_column <> 1 and i.type in (1,2)
                                                        order by key_ordinal)
                                                        ,
                                                        (select TOP 1 type_name + CASE WHEN col.user_type_id not in (48,52,56,58,59,62,104,127,106,108) THEN '(' + CONVERT(VARCHAR(10), col.max_length) + ')' ELSE '' END
                                                        from TYPED_COLUMNS col 
                                                        where col.object_id = i.object_id and i.type = 0 and (col.user_type_id in (48,52,56,58,59,62,104,127,106,108) or col.max_length between 1 and 800)
                                                        order by col.is_nullable desc)
                                                     ),
                    [includes / filters]   =   ISNULL(
                                                    REPLACE('INCLUDE (*)','*',   
                                                        REPLACE(
                                                            (select col.name as [data()]
                                                            from INDEX_COLUMNS col 
                                                            where col.object_id = i.object_id and col.index_id = i.index_id and col.is_included_column <> 0
                                                            order by key_ordinal, col.column_id
                                                            for xml path('')
                                                            ),  
                                                            ']  [', 
                                                            '], ['
                                                        )) + CHAR(13) 
                                                    ,''
                                                ) + 
                                                ISNULL ('WHERE ' + i.filter_definition + CHAR(13) , ''),

                    options               = ' WITH ('
                                            + ISNULL( CASE WHEN i.type IN (1, 2) THEN 'DROP_EXISTING = ON ' END, 'DROP_EXISTING = OFF')
                                            + ISNULL(', FILLFACTOR = '         + NULLIF(CAST(fill_factor AS VARCHAR(10)), '0') , '')
                                            + ISNULL(', PAD_INDEX = '         + CASE is_padded WHEN 1 THEN 'ON' ELSE 'OFF' END, '')
                                            + ISNULL(', ALLOW_ROW_LOCKS = '      + CASE allow_row_locks WHEN 1 THEN 'ON' ELSE 'OFF' END ,'')
                                            + ISNULL(', ALLOW_PAGE_LOCKS = '   + CASE allow_page_locks WHEN 1 THEN 'ON' ELSE 'OFF' END , '')
                                            + ISNULL(', IGNORE_DUP_KEY = '      + CASE ignore_dup_key WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' END, '')
                                            + ISNULL(', DATA_COMPRESSION = '   + data_compression_desc, '')
                                            + ISNULL(', STATISTICS_NORECOMPUTE = ' + CASE no_recompute WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' END, '')
                                            + CASE WHEN @Online = 1 AND SERVERPROPERTY('EngineEdition') = 3 THEN ', ONLINE = ON' ELSE '' END
                                            + ') ' + CHAR(13),
                    random_name            = 'MOVE_HELPER_' + @RANDOM_NAME
            from sys.indexes    i	(readpast)
            join sys.filegroups f   (readpast)   ON i.data_space_id = f.data_space_id 
            join sys.tables     obj (readpast)   ON i.object_id=obj.object_id
            join sys.schemas    sch            ON obj.schema_id=sch.schema_id
            left join sys.partitions part      ON i.object_id = part.object_id and i.index_id = part.index_id
            left join sys.stats stats         ON i.object_id=stats.object_id and i.index_id = stats.stats_id
            where sch.name <> 'sys' 
            and sch.name LIKE ISNULL(@SchemaFilter, '%')
            and obj.name LIKE ISNULL(@TableFilter,  '%')
            and f.name LIKE ISNULL(@FromFileGroup, '%')
            and (f.name <> @DataFileGroup 
                OR 
                COALESCE(FILEGROUP_NAME(obj.lob_data_space_id), @LobFileGroup,'PRIMARY') <> COALESCE(@LobFileGroup, FILEGROUP_NAME(obj.lob_data_space_id), 'PRIMARY'))
            and (   (i.type = 1 AND @ClusteredIndexes = 1)
                 OR (i.type = 2 AND @SecondaryIndexes = 1)
                 OR (i.type = 0 AND @Heaps = 1)
            )
        ) AS Script_Builder
        

    OPEN C;
    FETCH NEXT FROM C INTO @SQL;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @ProduceScript = 1
            SELECT @SQL
        ELSE
            EXEC (@SQL);
        
        FETCH NEXT FROM C INTO @SQL;
    END

    CLOSE C;
    DEALLOCATE C;
END
GO

EXEC sp_ms_marksystemobject 'sp_MoveTablesToFileGroup'