SQL Server->> generates partition function and partition scheme code for the time type

Sometimes a partition function is built on the job, but a partition function like a date can have a value of hundreds of if it's not going to kill me. So I wrote a bit of code to automatically generate a monthly partition function and a corresponding partition theme starting from 1990.

 Use [TestDB]GODECLARE @STR NVARCHAR(MAX)SET @STR = 'CREATE PARTITION FUNCTION [pf_rangebymonth_fromyear1990] (DATETIME) as RANGE left for VALUES ('DECLARE @STR2 NVARCHAR(MAX)SET @STR2 = 'CREATE PARTITION SCHEME [ps_rangebymonth_fromyear1990] as PARTITION [pf_rangebymonth_fromyear1990] to ('--generate corresponding partition function scriptSELECT @STR + STUFF((SELECT " "," " + CONVERT(VARCHAR,DATEADD(MONTHId-1,'1990-01-01'),121)         from [dbo].[Numbers]        WHEREId<= DATEDIFF(MONTH,'1990-01-01','2040-01-01')+1        ORDER  by [ID]         forXML PATH ("'), TYPE). Value ('.','varchar (max)') ,1,2,"')+ " ")' + CHAR( -) as 'partition Function Script'--generate corresponding partition scheme scriptSELECT @STR2 + STUFF((SELECT +', [PRIMARY]'          from [dbo].[Numbers]        WHEREId<= DATEDIFF(MONTH,'1990-01-01','2040-01-01')+2         forXML PATH ("'), TYPE). Value ('.','varchar (max)') ,1,2,"')+ ')' + CHAR( -) as 'Partition Scheme script'

SQL Server->> generates partition function and partition scheme code for the time type