On Premise and Cloud Database Knowledge Base

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.


USE DBName -- Change your dbname here
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*

Sample Output


Leave a Reply

Leave a Reply

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