Discussion in 'Performance Tuning for DBAs' started by cchitanu, May 7, 2004.
Does anybody have a script to get the filgroups for all tables in a database
Try exploring sp_help for the same. There is a section where it shells out the filegroup of the table. Use sp_MSforeachtable to execute that part for all the tables.
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
An sp_help on a table shows which filegroup the table is in. The 'Data_located_on_filegroup' column in the sp_hep results shows this. You can write a simple cursor to show this column value for all tables.
I use this one to find also index and statistics, you can change it to only tables.
select si.rows as 'filas', SO.Name as Tabla, 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 SFG.GroupName, SO.Name , SI.name
I think if you can have a look at SP_HELPFILEGROUP, you can have an idea about how to approach.'
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.
Separate names with a comma.