USE BingAdsMachines
GO
--CREATE PARTITION FUNCTION [BingAdsMachinesPF](DATE)
--AS RANGE RIGHT
--FOR VALUES('2012-12-1')
--CREATE PARTITION SCHEME [BingAdsMacheinesSchema]
--AS PARTITION [BingAdsMachinesPF]
--ALL TO ([primary])
--SELECT * FROM sys.partition_range_values
--select * from sys.partition_functions
--SELECT * FROM sys.partition_schemes
DECLARE @TmpDate DATE;
DECLARE @strTmpDate NVARCHAR(10);
DECLARE @SQL NVARCHAR(MAX);
SET @TmpDate = '2012-12-1'
WHILE(@TmpDate <= '2017-12-1')
BEGIN
SET @strTmpDate = CONVERT(NVARCHAR(10), @TmpDate, 121)
SET @SQL = N'IF NOT EXISTS(select value from '
+ 'sys.partition_range_values '
+ 'where function_id in '
+ '(select function_id from sys.partition_functions '
+ 'where name = ''BingAdsMachinesPF'') '
+ 'and CONVERT(datetime, value) = ''' + @strTmpDate + ''') '
+ 'BEGIN '
+ 'ALTER PARTITION SCHEME BingAdsMacheinesSchema '
+ 'NEXT USED [PRIMARY] '
+ 'ALTER PARTITION FUNCTION BingAdsMachinesPF() '
+ 'SPLIT RANGE (''' + @strTmpDate + ''') '
+ 'END'
PRINT ' Create partition range - ' + @strTmpDate
EXEC sp_executesql @SQL
SET @TmpDate = DATEADD(M, 1, @TmpDate)
END