Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

List Objects in a Filegroup in SQL Server 2005

I have tried hard to google a SQL query to find what objects exist in a specific filegroup, but in the end, I am not satisified with my findings.

I know there is an undocumented SP called sp_ObjectFileGroup, but I do not know how useful this is. My issue is when I create a table with a LOB column, I can put LOB column on a separate filegroup (say SECONDARY) while the other columns on another (say PRIMARY), and then I create a PK on the table. However, when I use sp_ObjectFileGroup, I always get "PRIMARY", meaning the object is on the primary filegroup, but how can I know what is on the SECONDARY filegroup?

I finally decide to rely on myself to figure this out, so after about 2 hours reading BOL, I came up with the following query, which I hope can be of help to my readers.

select fg=filegroup_name(a.data_space_id), tbl=object_name(p.object_id), idx=i.name
from sys.allocation_units a
inner join sys.partitions p
on p.partition_id = a.container_id
and p.object_id > 1024 -- arbitary, just to filter out system objects
and a.type = 2
left join sys.indexes i
on i.object_id = p.object_id
and i.index_id = p.index_id
union all
select fg=filegroup_name(a.data_space_id), tbl=object_name(p.object_id), idx=i.name
from sys.allocation_units a
inner join sys.partitions p
on p.hobt_id = a.container_id
and p.object_id > 1024 -- arbitary, just to filter out system objects
and a.type in (1, 3)
left join sys.indexes i
on i.object_id = p.object_id
and i.index_id = p.index_id

If you have better way, please share it.

 

Comments

Posted by chapman.tim on 5 April 2010

Hey, nice code.  I cleaned it up a bit and got rid of the UNION:

SELECT

FileGroup = FILEGROUP_NAME(a.data_space_id),

TableName = OBJECT_NAME(p.object_id),

IndexName = i.name

FROM sys.allocation_units a

INNER JOIN sys.partitions p ON a.container_id = CASE WHEN a.type in(1,3) THEN p.hobt_id ELSE p.partition_id END AND p.object_id > 1024

LEFT JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id

ORDER BY FileGroup

Posted by g_bucchi on 10 August 2010

Hi Tim, very nice code. Thanks to share it.

Posted by mishaluba on 24 October 2012

This was very helpful.  Thank you very much!

Posted by rx2526 on 16 January 2013

I have used this code. Thanks much!

Leave a Comment

Please register or log in to leave a comment.