Wednesday, February 2, 2011

SQL Server -- which objects reside on which filegroups?

This is a very handy statement joining sys.filegroups with sys.indexes, listing the filegroups that each of your objects reside upon.  Note, you can remove the o.type = 'U' to review the data file location for all objects:
o.[name] [TableName], 
i.[name] [IndexName]
f.[name] [FileGroup]
sys.indexes i INNER JOIN sys.filegroups f
       ON i.data_space_id = f.data_space_id 
      INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
i.data_space_id = f.data_space_id
AND o.type = 'U' -- Remove this to see other objects
AND <> 'sysdiagrams'
AND <> 'dtproperties'

These are some of the results from my own DBA database:

No comments:

Post a Comment