Check filegroup and file

By Vendoran on October 16th, 2007

Often when dealing with different filegroups or even different files in SQL Server 2005, I end up wanting to know where certain objects are logically and physically located.  Here’s a query that will give that information:

select
                'Object Name' = so.name,
                'Index Name' = si.name,
                'Type Description' = si.type_desc,
                'FileGroup Name' = sfg.Name,
                'System FileName' = sdf.Name
from
                sys.indexes si
                join sys.objects so on si.object_id = so.object_id
                join sys.schemas ss on so.schema_id = ss.schema_id
                join sys.database_files sdf on sdf.data_space_id = si.data_space_id
                join sys.filegroups sfg on sfg.data_space_id = sdf.data_space_id
where
                so.type = 'U'
                and so.Name not in ('dtproperties')
order by
                3 desc, 4, 1, 2
Categorized under: 2005, SQL Server.
Tagged with: no tags.

Leave a Response