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

Solarwinds

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

SQL SERVER - List All Objects Created on All Filegroups in Database fg1

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

SQL SERVER - List All Objects Created on All Filegroups in Database fg3

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

SQL SERVER - List All Objects Created on All Filegroups in Database fg2

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)

Solarwinds
, ,
Previous Post
SQL SERVER – Create Multiple Filegroup For Single Database
Next Post
SQLAuthority News – Summary of TechEd India 2009 – A Grand Event

Related Posts

42 Comments. Leave new

  • Just the topic that I was looking for. Thanks for the write-up Pinal!

    Reply
  • Hi, Can you post the query for SQL server to get the active objects?

    plz reply soon

    Reply
  • Was there a reason you decided to do a “i.data_space_id = f.data_space_id” in the WHERE instead of making this a second condition in the JOIN? Is it bad form to have two JOIN conditions? Thanks – I enjoy your blog.

    Reply
  • michael collins
    March 14, 2012 9:53 pm

    Thank you for your articles. Every time I have a problem, it seems like one of your articles has the solution. I appreciate you spending your time to write them.

    Reply
  • Use this script for finding objects in different file group, the script above from Pinal will give you only in primary file group. since the value for the data_space_id in sys.index is different from the sys.filegroups.

    select distinct O.name As TableName,I.name As IndexName
    ,I.type_desc As IndexType
    ,I.index_id As IndexID
    ,I.data_space_id as indexid
    ,DDS.data_space_id as SchemaID
    ,FG.name As FileGroupName
    From
    sys.indexes As I
    Inner Join
    sys.destination_data_spaces DDS
    on I.data_space_id = DDS.partition_scheme_id
    inner join
    sys.filegroups As FG
    On DDS.data_space_id = FG.data_space_id
    Inner Join
    sys.all_objects As O
    On I.[object_id] = O.[object_id]
    Where O.[type] = ‘U’

    Reply
  • Nice write up… How can I get a count of data rows in that second filegroup?

    Reply
  • WELLINGTHON AQUINO
    December 20, 2012 12:07 am

    Hi Pinar,

    Thanks for this help, really helped me a lot.

    As you explained, the basic script runs fine, however it just works with Tables.

    I want to propose the consideration of a little modification, cause the script below shows ALL OBJECTS (ie.: removing the ” WHERE o.type=’U’ ” clause), and if your looking for ALL objects by filegroups, ordering the results by filegroups could be appropiate.

    /* Get Details of Object on different filegroup
    Finding ALL OBJECTS*/
    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 f.name not like ‘PRIMARY’
    –AND o.type = ‘U’ — User Created Tables
    ORDER by f.name
    GO

    In my case, the object that existed in the filegroup was a full-text search index, and the original script based on tables didn´t show it up.

    Reply
  • Hi Pinal,

    Could you please help me to write a script which fetches all the FileGroups of all the databases?

    Advance Thanks

    Reply
    • Girish,

      Please find the script to fetch details about all the filegroups associated with all Databases

      select dbid, sa1.name as ‘File Name’, sa1.filename as ‘File Path’, fg1.name as ‘FileGroup Name’, groupid
      from sys.sysaltfiles sa1 inner join sys.filegroups fg1 on sa1.groupid = fg1.data_space_id
      order by dbid

      Neel

      Reply
  • Robespierre Maia Sá
    May 30, 2014 7:40 pm

    Script with improvements. Also displays allocated tables in partitions schema

    SELECT OBJECT_NAME(i.[object_id]) AS [ObjectName]
    ,i.[index_id] AS [IndexID]
    ,i.[name] AS [IndexName]
    ,i.[type_desc] AS [IndexType]
    ,pf.name AS [Partition Schema]
    ,f.[name] AS [FileGroup]
    ,d.[physical_name] AS [DatabaseFileName]
    FROM [sys].[indexes] i
    LEFT JOIN SYS.partition_schemes pf
    ON pf.[data_space_id] = i.[data_space_id]
    LEFT JOIN [sys].[filegroups] f
    ON f.[data_space_id] = i.[data_space_id]
    LEFT JOIN [sys].[database_files] d
    ON f.[data_space_id] = d.[data_space_id]
    LEFT JOIN [sys].[data_spaces] s
    ON f.[data_space_id] = s.[data_space_id]
    WHERE OBJECTPROPERTY(i.[object_id], ‘IsUserTable’) = 1
    –ORDER BY OBJECT_NAME(i.[object_id])
    — ,f.[name]
    — ,i.[data_space_id]
    ORDER BY [Partition Schema] DESC, F.name
    GO

    Reply
    • Very nice!

      Reply
      • Hi Dave, I have a small doubt, if we create multiple files in a filegroup or multiple filegroup with many files in it, weather all the logs generated only in a single log file for the whole database by deafult?

        I amm having this doubt, can you let me know.

      • What logs you are talking about? Log Records for transactions? If yes, then all would go to LDF file. Even if you have multiple, it would be written sequentially, one file at a time.

  • we have a database with 4 filegroups and multiple files within each group. Any way to take it the the actual file level not just the group

    Reply
  • Xavier Desroches
    March 21, 2017 5:40 pm

    Hey sir. I just had a case where I couldn’t delete a filegroup even after all the tables and indexes were have been moved to another filegroup. The problem were old/unused partition schemes still in the filegroups. Once dropped, it went well.

    Regards (Love the column)

    Reply
  • where has this information been hiding? exactly what I needed.

    Reply
  • I have two file groups primary and secondary, but 4 ndf files in secondary file group in different locations, how to identify my table location…? From above script I can identify file group secondary but how to check in which file ID my table exist…?

    Reply

Leave a Reply

Menu