查看表所在文件组

已分区的表：

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'