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 comment

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



Related Posts

Readonly database restoration failed
In one of my project I was working on SQL Server 2000 to SQL Server 2008 upgradation. It’s a side by side upgrade so I need to restore the databa...
Moving Index to a separate Filegroup
To improve the query performance we decided to have separate filegroup for indexes. We decided to move the indexes of very large tables indexes t...
Dropping a Subscriber from AG Publisher
Today one of my colleague reached out to me for an help to drop a subscriber from an publisher which is participating in Always On. We seen same...
powered by RelatedPosts