How to find on what Data File a SQL Server Table is on

  If you wanna find on which Data File your SQL Server Table is on, use the following query by providing the table name you are looking for:

select 
    OBJECT_NAME(p.object_id) as my_table_name, 
    u.type_desc,
    f.file_id,
    f.name,
    f.physical_name,
    f.size,
    f.max_size,
    f.growth,
    u.total_pages,
    u.used_pages,
    u.data_pages,
    p.partition_id,
    p.rows
from sys.allocation_units u 
    join sys.database_files f on u.data_space_id = f.data_space_id 
    join sys.partitions p on u.container_id = p.hobt_id
where 
    u.type in (1, 3)  and 
    OBJECT_NAME(p.object_id) = 'TableName'
GO

Copyright 2020. All Rights Reserved