which filegroup a table belongs to

• rdl98321

  SSC Rookie

  Points: 27

  More actions

  October 8, 2002 at 10:27 pm

  #75967

  Is there a way to query the system tables to find out which filegroup a table belongs to. Ive a view to find this out for indexes but I cant seem to find anything for tables. Can someone help me out.

• Chris Hedgate

  One Orange Chip

  Points: 25041

  More actions

  October 9, 2002 at 1:14 am

  #438130

  select groupid from sysindexes where name = 'yourtable'

  --

  Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

  http://www.sql.nu

  --
  Chris Hedgate http://www.hedgate.net/
  Contributor to the Best of SQL Server Central volumes
  Articles: http://www.sqlservercentral.com/columnists/chedgate/

• Michael S. Armentrout

  Ten Centuries

  Points: 1178

  More actions

  October 9, 2002 at 8:49 am

  #438167

  You can add filtering as needed.

  SELECT sf.groupid AS GroupID, substring(sf.groupname,1,30) AS FilegroupName,

  so.id AS ObjectID, substring(so.name,1,30) AS ObjectName,

  CASE si.indid

  WHEN 0 THEN 'TABLE'

  WHEN 1 THEN 'CLINDX'

  WHEN 255 THEN 'TEXT/IMAGE'

  ELSE 'NCLINDX'

  END AS IndexID, substring(si.name,1,30) AS IndexName

  FROM sysobjects so

  INNER JOIN sysindexes si ON so.id = si.id

  INNER JOIN sysfilegroups sf ON si.groupid = sf.groupid

  WHERE ObjectProperty(si.id, 'IsMSShipped') = 0 AND si.name NOT LIKE '_WA_Sys_%'

  ORDER BY sf.groupname, so.id, si.indid

  Be great!

  Michael

  
  Be great!
  Michael