SQL Server : T-SQL command that will determine in which filegroup, a particular table exist

It is very common to deal with situation where you have thousands of tables which are residing on multiple filegroups and you would like to know that in which filegroup, a particular table exist in your database.

Please change database table name accordingly.

**********************************************

/*Script for finding on which Filegroup the object resides in a database*/

USE Test

GO

SELECT object_name(i.[object_id]) as Name_of_Object,

i.name as Index_Name,

i.type_desc as Index_Type,

f.name as Name_of_Filegroup,

a.type as Object_Type,

f.type,

f.type_desc

FROM sys.filegroups as f

INNER JOIN sys.indexes as i

 ON f.data_space_id = i.data_space_id

INNER JOIN sys.all_objects as a

 ON i.object_id = a.object_id

WHERE a.type ='U' -- User defined tables only

AND object_name(i.[object_id]) ='employee' -- Specific object

GO

Hope it will help you .

Brgds,

Chhavinath Mishra

Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)