SQL Server- TSQL to see which object reside under which file groups?
To continue the FIND-FilegroupFull post on the filegroups, here is the most common question on the forums to get which object reside under which filegroup if there are multiple ones associated:
select si.rows as 'Rows', SO.Name as Table, SI.name as 'Index', SFG.groupname as 'Filegroup'
from sysobjects as SO
join sysindexes as SI
on SO.Id = SI.id
join sysfilegroups as SFG
on SI.GroupId = SFG.GroupId
order by si.rows desc, SO.Name , SI.name, SFG.GroupName
(Luis Martin from [SSP])
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.