declare
@databaseName varchar(100) =’mydb’,
@datapath varchar(2000) = ‘C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\’,
@period varchar (50) = ‘QUARTER’, — DAY, WEEK, MONTH, QUARTER, YEAR
@firstFilegroupDate date =’2024-01-01′,
@lastFilegroupDate date =’2027-02-01′,
@tempDate date,
@yearpart varchar (4),
@quarterpart varchar (1),
@monthpart varchar (3),
@daypart varchar(2),
@filegroupNameInit varchar(50)=’PART’,
@filegroupName varchar(100),
@fileName varchar(100),
@sql nvarchar(3000)=”,
@debug bit =0,
@execution bit=0,
@fgNo int=0,
@i int=0;
BEGIN
IF @period not in (‘DAY’, ‘WEEK’, ‘MONTH’, ‘QUARTER’, ‘YEAR’)
BEGIN
print ‘Invalid Period’
END
else
BEGIN
set @sql=N’select @fgNo= DATEDIFF(‘+@period+’,”’+cast(@firstFilegroupDate as varchar)+”’,”’+cast( @lastFilegroupDate as varchar)+”’) ;’;
–print @sql;
exec sp_executesql @sql, N’@fgNo int OUT’,@fgNo out;
print @fgNo
while @i<@fgNo
BEGIN
–print @i ;
SELECT @tempdate = case
when @period=’QUARTER’ then dateadd (qq,@i,@firstFilegroupDate)
when @period=’DAY’ then dateadd (dd,@i,@firstFilegroupDate)
when @period=’WEEK’ then dateadd (ww,@i,@firstFilegroupDate)
when @period=’MONTH’ then dateadd (mm,@i,@firstFilegroupDate)
when @period=’YEAR’ then dateadd (yy,@i,@firstFilegroupDate)
end ;
–print @tempdate;
select @filegroupName=@filegroupNameInit+
case
when @period =’QUARTER’ then cast (DATEPART(YEAR, @tempdate) as varchar) + ‘Q’+cast (DATEPART(QUARTER, @tempdate) as varchar)
when @period =’DAY’ then cast (DATEPART(YEAR, @tempdate) as varchar) + right(‘0’+cast (DATEPART(MONTH, @tempdate) as varchar),2) +right(‘0’+cast (DATEPART(DAY, @tempdate) as varchar),2)
when @period=’WEEK’ then cast (DATEPART(YEAR, @tempdate) as varchar) +’W’+right(‘0’+cast (DATEPART(iso_week, @tempdate) as varchar),2)
when @period=’MONTH’ then cast (DATEPART(YEAR, @tempdate) as varchar) +’M’+right(‘0’+cast (DATEPART(MONTH, @tempdate) as varchar),2)
when @period=’YEAR’ then cast (DATEPART(YEAR, @tempdate) as varchar)
end;
set @sql = ‘alter database ‘+@databaseName+’ add filegroup ‘+@filegroupName;
print @sql;
set @sql = ‘alter database ‘+@databaseName+’ add file (NAME=N”’+@filegroupName+”’,filename=”’+@datapath+@databaseName+’_’+@filegroupName+’.ndf”’ + ‘ , size=128MB, maxsize=unlimited,filegrowth=512MB) to filegroup ‘+@filegroupName;
print @sql;
set @i+=1;
END;
END;
END;
