MSSQL query to asses objects based on file_id

 SELECT DISTINCT

o.name AS object_name, o.type_desc AS object_type, OBJECT_SCHEMA_NAME(o.object_id) AS schema_name, au.data_space_id AS file_id, i.name AS index_name FROM sys.allocation_units au INNER JOIN sys.partitions p ON (au.type IN (1, 3) AND au.container_id = p.hobt_id) OR (au.type = 2 AND au.container_id = p.partition_id) INNER JOIN sys.objects o ON p.object_id = o.object_id LEFT JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id WHERE au.data_space_id = <your_file_id> -- Replace with your file_id ORDER BY o.name;

SELECT DISTINCT o.name AS object_name, o.type_desc AS object_type, OBJECT_SCHEMA_NAME(o.object_id) AS schema_name, df.file_id, df.name AS file_name, df.physical_name FROM sys.database_files df INNER JOIN sys.allocation_units au ON df.data_space_id = au.data_space_id INNER JOIN sys.partitions p ON (au.type IN (1, 3) AND au.container_id = p.hobt_id) OR (au.type = 2 AND au.container_id = p.partition_id) INNER JOIN sys.objects o ON p.object_id = o.object_id WHERE df.file_id = 3 -- Replace with your file_id ORDER BY o.name;