USE TestDB;
GO
-- add five new filegroups to the TestDB database
ALTER DATABASE TestDB
ADD FILEGROUP LeftFG;
GO
ALTER DATABASE TestDB
ADD FILEGROUP 20170110FG;
GO
ALTER DATABASE TestDB
ADD FILEGROUP 20170111FG;
GO
ALTER DATABASE TestDB
ADD FILEGROUP RightFG;
GO

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE RIGHT FOR VALUES ( '20170110', '20170111', '201701112' );
GO

CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (LeftFG, 20170110FG, 20170111FG, RightFG);
GO

ALTER DATABASE TestDB ADD FILEGROUP 20170112FG
GO  
ALTER DATABASE TestDB ADD FILE (NAME='20170112File', FILENAME='d:\data\20170112File') TO FILEGROUP 20170112FG;
GO

ALTER PARTITION SCHEME myRangePS1
NEXT USED 20170112FG;
GO
ALTER PARTITION FUNCTION myRangePF1 ()
SPLIT RANGE ('201701113');
GO

'201701112'

USE TestDB;
GO
-- add five new filegroups to the TestDB database
ALTER DATABASE TestDB
ADD FILEGROUP LeftFG;
GO
ALTER DATABASE TestDB
ADD FILEGROUP 20170110FG;
GO
ALTER DATABASE TestDB
ADD FILEGROUP 20170111FG;
GO
ALTER DATABASE TestDB
ADD FILEGROUP RightFG;
GO

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE RIGHT FOR VALUES ( N'2017-01-10', N'2017-01-11', N'20170112' );
GO

CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (LeftFG, 20170110FG, 20170111FG, RightFG);
GO

ALTER DATABASE TestDB ADD FILEGROUP 20170112FG
GO  
ALTER DATABASE TestDB ADD FILE (NAME='20170112File', FILENAME='d:\data\20170112File') TO FILEGROUP 20170112FG;
GO

ALTER PARTITION SCHEME myRangePS1
NEXT USED 20170112FG;
GO
ALTER PARTITION FUNCTION myRangePF1 ()
SPLIT RANGE (N'2017-01-13');
GO

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE RIGHT FOR VALUES ( N'2017-01-10', N'2017-01-11', N'20170112' );

--	Статистика по файлгруппам и файлам
SELECT	fg.name as FileGroupName,
		fg.is_read_only,
		df.name as LogicalFileName,
		df.physical_name as FileName,
		--r.value as [Native range],
		case when f.type = 'R' THEN '>= ' ELSE '<= ' END + convert(varchar(24), r.value, 120) AS [Native range],
		sum(p.rows) as rows,
		max(convert(decimal(18,3), convert(decimal(18,3), df.size) * 8196 / (1024*1024*1024))) as [FileSize_GB],
		max(convert(decimal(18,3), convert(decimal(18,3), FILEPROPERTY(df.name, 'SpaceUsed')) * 8196 / (1024*1024*1024))) as [SpaceUsed_GB]
FROM sys.indexes i 
	JOIN sys.tables t ON (t.object_id = i.object_id)
	JOIN sys.partition_schemes ps ON (i.data_space_id = ps.data_space_id)
	JOIN sys.partition_functions f ON f.function_id = ps.function_id
	JOIN sys.destination_data_spaces dds ON (ps.data_space_id = dds.partition_scheme_id)
	LEFT JOIN sys.partition_range_values r on r.function_id = f.function_id and r.boundary_id + cast(f.boundary_value_on_right as int) = dds.destination_id
	JOIN sys.partitions p ON p.object_id = i.object_id and p.index_id = i.index_id  and p.partition_number = dds.destination_id
	JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
	JOIN sys.database_files df ON df.data_space_id = fg.data_space_id
WHERE (i.index_id IN (0,1))
GROUP BY fg.name, fg.is_read_only, df.name, df.physical_name, case when f.type = 'R' THEN '>= ' ELSE '<= ' END + convert(varchar(24), r.value, 120) --r.value
ORDER BY FileGroupName, LogicalFileName