Ага, ну вот.
Сразу, чтобы бы ввести понимание - работает только в Юконе (уровень совместимости неважен), причем Энтерпрайз версии (Или Девелопер - там где партиционирование разрешенно).
Сначала надо создать файловые группы с файлами данных (у меня DATAGROUP1, DATAGROUP2 и т.д.)
Потом функцию и схему
Код:
CREATE PARTITION FUNCTION axDataAreaPF (varchar(3))
AS RANGE LEFT FOR VALUES ('mce', 'mso', 'mea', 'mpc', 'pal', 'pgt', 'psa','pgh');
GO
CREATE PARTITION SCHEME axDataAreaPS
AS PARTITION axDataAreaPF
TO ([DATAGROUP1], [DATAGROUP2], [DATAGROUP3], [DATAGROUP4], [DATAGROUP5], [DATAGROUP6], [DATAGROUP7], [DATAGROUP8], [DATAGROUP9], [DATAGROUP10]);
GO
Где у нас 'mce', 'mso', 'mea', 'mpc', 'pal', 'pgt', 'psa','pgh' - это разные DataArea
По особенностям операторов лучьше почитать в хелпе - есть нюансы с LEFT и лишней группой в схеме.
Далее, само интересное. Этим скриптом я перебираю объекты, проверяю, что они еще не партициионированны и, собственно, do it. Скрипт можно запускать много раз - если ничего не изменилось или не было ошибок, то и делать ничего не будет.
Код:
USE AX64SP3;
SET LOCK_TIMEOUT -1;
GO
DECLARE @table_name varchar(100), @table_id int, @table_partition_type varchar(2), @table_partition_name varchar(100)
DECLARE @index_name varchar(100), @index_type tinyint, @index_is_unique tinyint, @index_is_primary_key tinyint, @index_is_disabled tinyint, @index_allow_row_locks tinyint, @index_allow_page_locks tinyint, @index_partition_type varchar(2), @index_partition_name varchar(100)
DECLARE @column_name varchar(100), @column_is_descending_key tinyint, @column_first tinyint
DECLARE @SQL as nvarchar(4000)
DECLARE table_cursor CURSOR FOR
select DISTINCT o.name, o.object_id, ds.type as type, ISNULL(pf.name, ds.name) as name
from
sys.partitions AS p
INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.objects AS o ON o.object_id = i.object_id
INNER JOIN sys.data_spaces ds ON i.data_space_id=ds.data_space_id
LEFT JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
where o.type='U' and i.type=0 and SCHEMA_NAME(o.schema_id) = 'dbo'
ORDER BY o.name
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table_name, @table_id, @table_partition_type, @table_partition_name
WHILE @@FETCH_STATUS = 0
BEGIN
IF
EXISTS(select [name] FROM sys.columns WHERE object_id = @table_id AND UPPER(name) = 'DATAAREAID')
AND NOT @table_name LIKE 'SYS%'
BEGIN
PRINT '--' + @table_name
IF NOT EXISTS (Select [name] FROM sys.indexes WHERE [type] = 1 AND object_id = @table_id) AND @table_partition_type = 'FG'
BEGIN
PRINT '----MOVE TO PARTITION'
SELECT @SQL = 'CREATE CLUSTERED INDEX tmp_cluster ON ' +@table_name+ '(DATAAREAID) ON axDataAreaPS(DATAAREAID);'
--PRINT @SQL
EXEC(@SQL)
SELECT @SQL = 'DROP INDEX tmp_cluster ON ' + @table_name + ';'
--PRINT @SQL
EXEC(@SQL)
END
Declare index_cursor CURSOR FOR
Select i.name, i.type, i.is_unique, i.is_primary_key, i.is_disabled, i.allow_row_locks, i.allow_page_locks, ds.type as type, ISNULL(pf.name, ds.name) as name
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.objects AS o ON o.object_id = i.object_id
INNER JOIN sys.data_spaces ds ON i.data_space_id=ds.data_space_id
LEFT JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE i.object_id = @table_id and i.type>0
Order by i.type
OPEN index_cursor
FETCH NEXT FROM index_cursor INTO @index_name, @index_type, @index_is_unique, @index_is_primary_key, @index_is_disabled, @index_allow_row_locks, @index_allow_page_locks, @index_partition_type, @index_partition_name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @index_partition_type = 'FG'
BEGIN
PRINT '----' + @index_name
SELECT @SQL =
CASE WHEN @index_is_primary_key = 1 THEN
'ALTER TABLE ' + @table_name + ' DROP CONSTRAINT ' + @index_name +' WITH (ONLINE = OFF); '
+ ' ALTER TABLE ' + @table_name + ' WITH NOCHECK ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY ' +CASE WHEN @index_type = 1 THEN ' CLUSTERED ' ELSE ' ' END
ELSE
'CREATE ' + CASE WHEN @index_type = 1 THEN 'CLUSTERED' WHEN @index_is_unique =1 THEN 'UNIQUE' ELSE '' END +' INDEX ' + @index_name + ' ON '+ @table_name
END
Declare column_cursor CURSOR FOR
Select t3.[name], t2.is_descending_key
FROM sys.indexes t1
INNER JOIN sys.index_columns t2 ON t1.object_id=t2.object_id AND t1.index_id = t2.index_id
INNER JOIN sys.columns t3 ON t1.object_id=t3.object_id AND t2.column_id = t3.column_id
WHERE t1.[name] =@index_name and t1.object_id = @table_id
Order by t2.index_column_id
SELECT @SQL = @SQL + '('
SELECT @column_first = 1
OPEN column_cursor
FETCH NEXT FROM column_cursor INTO @column_name, @column_is_descending_key
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = @SQL + CASE WHEN @column_first=1 THEN '' ELSE ', ' END + @column_name + CASE WHEN @column_is_descending_key = 1 THEN ' DESC' ELSE ' ' END
SELECT @column_first = 0
FETCH NEXT FROM column_cursor INTO @column_name, @column_is_descending_key
END
CLOSE column_cursor
DEALLOCATE column_cursor
SELECT @SQL = @SQL +
CASE WHEN @index_is_primary_key = 1 THEN
') WITH (FILLFACTOR = 75, ONLINE = OFF) ON axDataAreaPS(DATAAREAID);'
ELSE
') WITH (DROP_EXISTING = ON, ONLINE = OFF) ON axDataAreaPS(DATAAREAID);'
END
--PRINT @SQL
EXEC(@SQL)
END
FETCH NEXT FROM index_cursor INTO @index_name, @index_type, @index_is_unique, @index_is_primary_key, @index_is_disabled, @index_allow_row_locks, @index_allow_page_locks, @index_partition_type, @index_partition_name
END
CLOSE index_cursor
DEALLOCATE index_cursor
PRINT 'GO'
END
FETCH NEXT FROM table_cursor INTO @table_name, @table_id, @table_partition_type, @table_partition_name
END
CLOSE table_cursor
DEALLOCATE table_cursor
Вместо EXEC можно расскомментировать PRINT - тогда результатом работы будет SQL скрипт, который можно разглядывать и выполнять по частям ручками.
AS IS, конечно