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