Determine which objects exist in a particular filegroup

This script will help you to determine the objects existence in a filegroup. You can get on which file group does a object exists. Change dbname before using the script.

Script

USE DBName -- Change your dbname here
GO
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id --* New FileGroup*
GO

Sample Output

object_filegroup

Leave a Reply

Your email address will not be published. Required fields are marked *

*