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;<\/p>\n","protected":false},"excerpt":{"rendered":"
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;BEGINIF @period not in (‘DAY’, ‘WEEK’, ‘MONTH’, ‘QUARTER’, ‘YEAR’)BEGINprint ‘Invalid Period’ENDelseBEGINset @sql=N’select @fgNo= DATEDIFF(‘+@period+’,”’+cast(@firstFilegroupDate as varchar)+”’,”’+cast( […]<\/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-234","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\/234","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=234"}],"version-history":[{"count":3,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/234\/revisions"}],"predecessor-version":[{"id":238,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/234\/revisions\/238"}],"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=234"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/categories?post=234"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/tags?post=234"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}