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


Leave a comment or a question in the comment area bellow if you fell like


Be a sport and

Search

Loading... Please wait

Subscribe to our Newsletter

Be a sport and

x