As a SQL Server enthusiast, I always strive to provide valuable information to my readers. Recently, I received a question from one of my readers, Joginder “Jogi” Padiyala, asking how to determine which object belongs to which filegroup in SQL Server. In this blog post, I will address this question and provide a step-by-step guide to finding this information.

First, let’s start by creating a database with multiple filegroups:

CREATE DATABASE [FGTest] 
ON PRIMARY (
    NAME = N'FGTest',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FGTest.mdf',
    SIZE = 3072KB,
    FILEGROWTH = 1024KB
), 
FILEGROUP [Secondary] (
    NAME = N'FGTest_2',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FGTest_2.ndf',
    SIZE = 3072KB,
    FILEGROWTH = 1024KB
) 
LOG ON (
    NAME = N'FGTest_log',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FGTest_log.ldf',
    SIZE = 1024KB,
    FILEGROWTH = 10%
);

Next, let’s create a table on the primary filegroup:

USE [FGTest];
CREATE TABLE [dbo].[TestTable] (
    [ID] [int] NOT NULL,
    [Col1] [varchar](50) NOT NULL,
    [Col2] [varchar](50) NOT NULL,
    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY];

Now, let’s create a non-clustered index on the secondary filegroup:

CREATE NONCLUSTERED INDEX [IX_TestTable_Second] ON [dbo].[TestTable] ([Col1] ASC) ON [Secondary];

Now that we have our database and objects set up, let’s find out which objects belong to which filegroup. We can achieve this by querying the system tables in SQL Server.

To find all the user-created tables and their corresponding filegroups, we can use the following query:

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
AND o.type = 'U'; -- User Created Tables

If we want to specifically find objects located on a particular filegroup, we can modify the query as follows:

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
AND i.data_space_id = 2; -- Filegroup

By executing these queries, we can easily determine which objects belong to which filegroup in SQL Server.

I hope this explanation helps you understand how to find the relationship between objects and filegroups in SQL Server. If you have any further questions or topics you’d like me to cover, please feel free to reach out. Happy coding!