I have this query:

select sys.tables.name,sys.data_spaces.name
from sys.tables,sys.data_spaces
where sys.tables.lob_data_space_id = sys.data_spaces.data_space_id
order by sys.tables.name

Which gives me the tables with LOB object locations, but not the location of
the table itself. I've spent a fair amount of time looking, and I just
can't seem to find a query that generates the output I'm looking for which
is tablename/filegroup name. What am I missing?

M.

Hi Michael

The table's dataspace is stored in sys.indexes. Look for the row in
sysindexes where the object_id is the table's object_id and the index_id is
0 (for a heap) or 1 (if the table has clustered index).

Note that dataspace may not map to just one filegroup. It may map to a
partition scheme which is a collection of filegroups.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com


"Michael B." <hrast@swbell.net> wrote in message
news:u3Qs6aUDJHA.4816@TK2MSFTNGP06.phx.gbl...
>I have this query:
>
> select sys.tables.name,sys.data_spaces.name
> from sys.tables,sys.data_spaces
> where sys.tables.lob_data_space_id = sys.data_spaces.data_space_id
> order by sys.tables.name
>
> Which gives me the tables with LOB object locations, but not the location
> of the table itself. I've spent a fair amount of time looking, and I just
> can't seem to find a query that generates the output I'm looking for which
> is tablename/filegroup name. What am I missing?
>
> M.
>