Automatic Partition<\/p>\n\n\n\n
<\/p>\n\n\n\n <\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":1,"featured_media":104,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[50],"class_list":["post-109","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mssql","tag-partitions"],"blocksy_meta":[],"_links":{"self":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/109","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/comments?post=109"}],"version-history":[{"count":2,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/109\/revisions"}],"predecessor-version":[{"id":117,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/109\/revisions\/117"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media\/104"}],"wp:attachment":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media?parent=109"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/categories?post=109"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/tags?post=109"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}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><\/code><\/p>\n\n\n\n