已分区的表
SELECT DISTINCT
t.name AS TableName
,ps.name AS PSName
,fg.name AS FileGroupName
,f.name AS [FileName]
,f.physical_name AS [FilePhysicalName]
--,dds.destination_id AS PartitionNumber --去除注释即可显示文件的分区数
FROM sys.tables AS t
INNER JOIN sys.indexes AS i
ON (t.object_id = i.object_id)
INNER JOIN sys.partition_schemes AS ps
ON (i.data_space_id = ps.data_space_id)
INNER JOIN sys.destination_data_spaces AS dds
ON (ps.data_space_id = dds.partition_scheme_id)
INNER JOIN sys.filegroups AS fg
ON dds.data_space_id = fg.data_space_id
INNER JOIN sys.database_files f
ON f.data_space_id = fg.data_space_id
--WHERE t.name = 'tableName'
未分区的表
SELECT t.[name], 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.tables AS t
ON i.[object_id] = t.[object_id]
--WHERE t.name='tablename'
另一种方法
SELECT o.[name] N'对象名', o.[type], i.[name] N'索引名', i.[index_id], f.[name] N'文件组名'
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
--o.type = 'U' -- UDT
--AND
i.data_space_id='4' --文
--AND f.name='DisLocalPureDataThree'