SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name] FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id

go
use master
go

if exists (select * from sys.databases where name = N'TestGroup')
	drop database TestGroup
go

CREATE DATABASE TestGroup
ON
PRIMARY  
    ( NAME = TestBackup_PRIMARY,
    FILENAME = 'D:\SQLDATA\TestGroup.mdf'),
FILEGROUP SECONDARY 
    ( NAME = TestGroup_SECONDARY,
	FILENAME = 'D:\SQLDATA\TestGroup_SECONDARY.ndf')
LOG ON 
   (NAME = TestGroup_Log,
    FILENAME = 'D:\SQLDATA\TestGroup_Log.ldf')
GO

--	Создаём таблицы и данные
use TestGroup
go
create table dbo.Test(id int) ON [SECONDARY]
go
alter database TestGroup REMOVE FILE TestGroup_SECONDARY
go

alter database TestGroup REMOVE FILEGROUP [SECONDARY]
--	WTF???
--	Msg 5042, Level 16, State 8, Line 30
--	The filegroup 'SECONDARY' cannot be removed because it is not empty.
go

drop table dbo.Test
go
alter database TestGroup REMOVE FILEGROUP [SECONDARY]
go

if exists (select * from sys.databases where name = N'TestGroup')
	drop database TestGroup
go

create table dbo.Test(id int, [image] image) ON [SECONDARY] TEXTIMAGE_ON [SECONDARY]
go
create clustered index [IDC] on dbo.Test ([id]) ON [PRIMARY]
GO

SELECT sau.* ,sp.* ,fg.*
FROM sys.allocation_units sau 
INNER JOIN sys.partitions sp
	ON (sau.[type] in (1,3) AND sau.[container_id] = sp.[hobt_id]) OR (sau.[type] in (2) AND sau.[container_id] = sp.[partition_id])
INNER JOIN sys.filegroups fg
	ON sau.[data_space_id] = fg.[data_space_id]
WHERE sp.[object_id] = OBJECT_ID('dbo.Test')

use master
go

if exists (select * from sys.databases where name = N'TestGroup')
	drop database TestGroup
go

CREATE DATABASE TestGroup
ON
PRIMARY  
    ( NAME = TestBackup_PRIMARY,
    FILENAME = 'D:\SQLDATA\TestGroup.mdf'),
FILEGROUP SECONDARY 
    ( NAME = TestGroup_SECONDARY,
	FILENAME = 'D:\SQLDATA\TestGroup_SECONDARY.ndf')
LOG ON 
   (NAME = TestGroup_Log,
    FILENAME = 'D:\SQLDATA\TestGroup_Log.ldf')
GO

--	Создаём таблицы и данные
use TestGroup
go
create table dbo.Test(id int) ON [SECONDARY]
go
alter database TestGroup REMOVE FILE TestGroup_SECONDARY
go

alter database TestGroup REMOVE FILEGROUP [SECONDARY]
--	WTF???
--	Msg 5042, Level 16, State 8, Line 30
--	The filegroup 'SECONDARY' cannot be removed because it is not empty.
go
create clustered index [IDC] on dbo.Test ([id]) ON [PRIMARY]
go
alter database TestGroup REMOVE FILEGROUP [SECONDARY]
go

use master
go
if exists (select * from sys.databases where name = N'TestGroup')
	drop database TestGroup
go

ALTER DATABASE [TestGroup] MODIFY FILEGROUP [SECONDARY] DEFAULT;
GO

select	*
from sys.partition_functions f 
	JOIN sys.partition_schemes ps ON ps.function_id = f.function_id
	JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id 
	JOIN sys.filegroups fg ON fg.data_space_id = dds.data_space_id
	JOIN sys.partition_range_values r on r.function_id = f.function_id and dds.destination_id = r.boundary_id + cast(f.boundary_value_on_right as int)
	left join sys.database_files sf on sf.data_space_id = ps.data_space_id