Determining which tables are in which file group via T-SQL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michael B. - 15 Aug 2008 17:35 GMT

I'm trying to figure out how to list what file group every table is in. I
thought this query was it :

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

Until I realised that it only tells me where the LOB objects are. I've been
beating my head against a wall on this, any guidance?

M.

Reply to this Message

Tariq - 27 Aug 2008 15:39 GMT

try below query,

SELECT OBJECT_NAME(SI.OBJECT_ID) AS OBJECTNAME
,SI.DATA_SPACE_ID AS FILE_GROUP_ID
,SFG.NAME AS FILE_GROUP_NAME
,SF.NAME AS LOGICAL_FILE_NAME
,SF.FILENAME AS PHYSICAL_FILE_NAME
FROM SYS.INDEXES SI
INNER JOIN SYS.FILEGROUPS SFG ON SFG.DATA_SPACE_ID=SI.DATA_SPACE_ID
INNER JOIN SYS.SYSFILES SF ON SF.GROUPID = SFG.DATA_SPACE_ID
WHERE SI.INDEX_ID IN (0,1)

for further queries about file group,
http://sqlserver4me.blogspot.com/2008/08/how-to-view-association-among-file.html

Reply to this Message