Automatic Partition
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
use <Database>
Declare
@nextRange date,
@sql nvarchar(3000),
@maxBoundary date,
@tableList varchar(1000) ='Table1,Table2,Table3',
@tableName varchar(100),
@partitionFunction varchar(100),
@partitionScheme varchar(100),
@PartitionedTables as cursor ,
@debug bit =0,
@monthsPartitionFuture smallint =7,
@filegroup = 'Primary' ;
set @PartitionedTables = cursor for
select * from STRING_SPLIT (@tableList,',');
OPEN @PartitionedTables;
FETCH NEXT FROM @PartitionedTables into @tableName;
WHILE @@FETCH_STATUS=0
BEGIN
IF @debug=1 print @tableName;
SELECT @maxBoundary=max(cast (rv.value as date)) ,@nextRange=dateadd(MONTH,1,cast (max(value) as date)),@partitionFunction=f.name,@partitionScheme=ps.name
FROM sys.partitions p
INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.objects o ON p.object_id = o.object_id
INNER JOIN sys.system_internals_allocation_units au ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions f ON f.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.partition_range_values rv ON f.function_id = rv.function_id AND p.partition_number = rv.boundary_id
WHERE o.name=@tableName
group by f.name,ps.name
IF @debug=1 print cast(@maxBoundary as varchar) +'---'+@TableName
WHILE @maxBoundary<dateadd(month,@monthsPartitionFuture,getdate())
BEGIN
select @sql = 'ALTER PARTITION SCHEME '+@partitionScheme +' NEXT USED '+ @filegroup +' ;
ALTER PARTITION FUNCTION '+@partitionFunction+'() SPLIT RANGE (N'''+cast (@nextRange as varchar)+''');';
print @sql;
exec sp_executesql @sql;
SELECT @maxBoundary=max(cast (rv.value as date)) ,@nextRange=dateadd(MONTH,1,cast (max(value) as date))
FROM sys.partitions p
INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.objects o ON p.object_id = o.object_id
INNER JOIN sys.system_internals_allocation_units au ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions f ON f.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.partition_range_values rv ON f.function_id = rv.function_id AND p.partition_number = rv.boundary_id
WHERE o.name=@tableName
group by f.name,ps.name
IF @debug=1 print @maxBoundary
END;
FETCH NEXT FROM @PartitionedTables into @tableName;
END;
CLOSE @PartitionedTables
-----------------------------------------------