"Subodh" <sg****@triversity.com> wrote in message
news:a4*************************@posting.google.co m...
Hi,
I want to find out in my SP the filegroup associated with a particular
table? I was not able to establish the relationship between sysobjects
and sysfilegroups table. Is there any other way to find filegroup of a
table in T_SQL?
Also lets say there are 3 dtatfiles in a filegroup 'FG1' and table
'xyz' is created on filegroup 'FG1' - Is there any way to find out the
datafile's names on which table xyz's data is scattered?
Thanks in Advance,
Subodh
For the first part of your question, unfortunately there is no supported
function to return this information. The only documented way to find the
filegroup is with sp_help, or by using the .FileGroup property of the table
object via SQL-DMO. However, sp_help calls an undocumented procedure called
sp_objectfilegroup to get the information - you could re-use the code from
that procedure in your own code (it joins sysindexes on sysfilegroups, which
works even for tables with no idexes).
As for the second part of your question, SQL Server always spreads data
across all files in a filegroup - see the topic "Using Files and Filegroups"
in BOL.
Simon