Going physical with table: File Group or Partition Info

--
-- All tables
select
 s.name as SchemaName
 ,t.name as TableName
 ,i.type_desc as TableStorageType
 ,case when f.name is null then 1 else 0 end as IsPartitioned
 ,f.name as FileGroupName
 ,ps.name as PartitionSchema
 ,pf.name as PartitionFunction
 ,c.name as PartionColumn
 -- on cluase for the table creation statement
 ,'ON '+case when f.name is not null then  f.name else ps.name+'('+c.name+')' end as OnClause
 from sys.schemas s inner join sys.tables t on
   t.schema_id=s.schema_id
   -- check table storage type
   inner join  sys.indexes i on  
   (i.object_id = t.object_id
   -- 0: heap
   -- 1: clusterd
   and i.index_id in (0,1))
   -- will have value for non-partioned table
   LEFT JOIN sys.filegroups f ON
   i.data_space_id = f.data_space_id
   -- will have value for partitioned table
   left join sys.partition_schemes ps on 
   i.data_space_id = ps.data_space_id
   left join sys.partition_functions pf ON ps.function_id = pf.function_id
   left join  sys.index_columns  ic on
   (-- identify partioned column
   ic.partition_ordinal > 0
   and ic.index_id = i.index_id
   and ic.object_id = t.object_id)
   left join  sys.columns c on
   (c.object_id = ic.object_id
   and c.column_id = ic.column_id)
 order by 1,2

For non-partitioned tables:
SELECT s.name as SchemaName
  ,o.[name] as TableName
  ,i.type_desc as TableStorageType
  ,f.[name] as FileGroupName
  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]
    INNER JOIN sys.schemas s ON
    s.schema_id=o.schema_id
  WHERE i.data_space_id = f.data_space_id
    AND o.type = 'U' -- User Created Tables
    AND i.index_id in (0,1)
  ORDER BY 1, 2

For partitioned tables:

select
 s.name as SchemaName
 ,t.name as TableName
 ,i.type_desc as TableStorageType
 ,ps.name as PartitionSchema
 ,pf.name as PartitionFunction
 ,c.name as PartionColumn
 from sys.schemas s inner join sys.tables t on
    t.schema_id=s.schema_id
   -- check table storage type
   inner join  sys.indexes i on  
    (i.object_id = t.object_id
    -- 0: heap
    -- 1: clusterd
    and i.index_id in (0,1))
   inner join sys.partition_schemes ps
    on  i.data_space_id = ps.data_space_id
   inner join sys.partition_functions pf ON ps.function_id = pf.function_id
   inner join  sys.index_columns  ic on
    (-- identify partioned column
    ic.partition_ordinal > 0
    and ic.index_id = i.index_id
    and ic.object_id = t.object_id)
   inner join  sys.columns c on
    (c.object_id = ic.object_id
    and c.column_id = ic.column_id)
 order by 1,2