how can i to determine a file or filegroup for storing table data?

Question

i want to determine a specific file or file group for saving a specific table in a database. how can i do this?

Answer 1

So you are going to need to be more specific.  You can get a list of filegroups in the database along with the logical files from sys.master_files and sys.filegroups.  Here is my query, which includes logs and totals (but not how much space is available, which you may be asking):

select  case when grouping(db_name(database_id)) = 1 
   then '--TOTAL--' 
   else db_name(database_id) end AS database_name, 
 case when grouping(SUBSTRING(database_files.physical_name,1,1)) = 1 
   then '--TOTAL--' 
   else SUBSTRING(database_files.physical_name,1,1) end as driveLetter,
    case when grouping(filegroups.name) = 1 Then '--TOTAL--'
   when filegroups.name is null then '--LOGS--'
   else filegroups.name end as filegroup_name, 
    case when grouping(database_files.name) = 1 
   then '--TOTAL--' 
   else database_files.name end as database_file_name,
    sum(database_files.size * 8 )   as sizeInK
from sys.master_files as database_files
  left outer join sys.filegroups
   on database_files.data_space_id = filegroups.data_space_id
where database_id = db_id()
group by db_name(database_id), SUBSTRING(database_files.physical_name,1,1), filegroups.name, database_files.name with rollup

Returns on one of my test databases with one filegroup:

database_name     driveLetter filegroup_name   database_file_name  sizeInK
----------------- ----------- ---------------- -------------------- -----------
SequenceDemos     C           --LOGS--         SequenceDemos_log    512000
SequenceDemos     C           --LOGS--         --TOTAL--            512000
SequenceDemos     C           PRIMARY          SequenceDemos        2048000
SequenceDemos     C           PRIMARY          --TOTAL--            2048000
SequenceDemos     C           --TOTAL--        --TOTAL--            2560000
SequenceDemos     --TOTAL--   --TOTAL--        --TOTAL--            2560000
--TOTAL--         --TOTAL--   --TOTAL--        --TOTAL--            2560000

So that will tell you what groups exist, is that what you wanted?

---

Louis

Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

Answer 2

Hi iman rahiminia,

For a table, there can be three types of objects: clustered index, non-clustered index, heap (a table without clustered index). When you say a file saving a specific table, it refers to where the objects belonged to that table stored. Please pay attention to this blog about this topic: SQL SERVER – List All Objects Created on All Filegroups in Database.

---

Stephanie Lv

TechNet Community Support

Answer 3

In addition tom Louis  reply

create database test
  on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
  (name = 'datafile2', filename = 'c:\temp\datafile2')
log on
  (name = 'logfile1', filename = 'c:\temp\logfile1')
go

use test
go

create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg

select
  object_name(i.id) as table_name,
  groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
 and i.indid < 2
 and i.groupid = s.groupid

table_name filegroup
---------- ---------
t1         PRIMARY
t2         PRIMARY
t3         user_fg

alter database test modify filegroup user_fg default
create table t4(col1 int)

select
  object_name(i.id) as table_name,
  groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'),object_id('t4'))
 and i.indid < 2
 and i.groupid = s.groupid

table_name filegroup
---------- ---------
t1         PRIMARY
t2         PRIMARY
t3         user_fg
drop database test

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/