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)
44 Comments. Leave new
Just the topic that I was looking for. Thanks for the write-up Pinal!
Hi, Can you post the query for SQL server to get the active objects?
plz reply soon
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.
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.
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’
This is exactly what I Was looking for. Thank you!
Awesome, Its a Cheers.
Nice write up… How can I get a count of data rows in that second filegroup?
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.
Hi Pinal,
Could you please help me to write a script which fetches all the FileGroups of all the databases?
Advance Thanks
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
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
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
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)
Thanks Xavier.
where has this information been hiding? exactly what I needed.
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…?
Hi Dave
Thanks for sharing this. I have two primary files, a secondary, ternary and log files. As i have two primary filegroups how can i find which tables goes into which primary file. Is there any option to figure that out.
Helpful…thanks! –Kevin3NF