SQL SERVER – List All Objects Created on All Filegroups in Database

When I pen down any article I always keep my readers in my mind. With every topic of SQL server I cover, I try to bring readers closer to this technology. So, whenever I receive follow up questions from my readers I am exhilarated! Sometime back I had covered a topic – SQL SERVER – Create Multiple Filegroup For Single Database, for which I received a number of follow up questions. In this post I would like to discuss on a question from one of the readers Joginder “Jogi” Padiyala.

“How can I find which object belongs to which filegroup. Is there any way to know this?”

Well, finding out which object belongs to which table is very simple.
Let us first create a database having multiple filegroups.

/* Create Database
Please note that there are two Filegroups for newly created datbase.
MDF - Primary and LDF on Secondary */
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%)
GO

Now create a table on primary filegroup.

/* Create New table on Primary Filgroup */
USE [FGTest] GO
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] GO

Next, create non-clustered index on secondary filegroup.

/* Create Non Clustered Index on Secondary FileGroup */
CREATE NONCLUSTERED INDEX [IX_TestTable_Second] ON [dbo].[TestTable] (
[Col1] ASC
) ON [Secondary] GO

After that, we will run the following T-SQL and determine where all the objects are located on filegroup. We have already created a non-clustered index on our table. Now, non-clustered table creates its own table, which consists of pointers to clustered index. For the same reason, we will search with additional condition of object type, which will list only user created tables. Non- clustered table should be part of user created table group.

/* Get Details of Object on different filegroup
Finding User Created Tables*/
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
GO

Let us observe the same query again with different WHERE condition. Here we are retrieving everything that is on secondary filegroup. It is very easy to identify filegroup name and data by just selecting everything from system table sys.filegroups.

/* Get Detail about Filegroups */
SELECT *
FROM sys.filegroups
GO

In our case, secondary filegroup has data_space_id as 2. Now, we will run the following query and figure out which objects are located on filegroup 2.

/* Get Details of Object on different filegroup
Finding Objects on Specific Filegroup*/
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
GO

I hope this answers Jogi’s question and is helpful for those who were curious to know the answer to this topic.

Reference : Pinal Dave (https://blog.sqlauthority.com)