Many readers send me many different queries from time to time..I try to answer them through mail or through Blogs. This has really helped me to broaden my knowledge and it always feels good if I can help any other professional to understand this technology in better way. So, Thank You all the readers !!

Today I am writing about a topic that has been asked to me many times and I have responded to it through mails and other channels..but today again I have received the request and I thought it would be better if I can write a Blog about it and so here it is ..

From time to time, we have to find all the tables that are there in different filegroups for various performance tuning activities.

For todays topic I am using AdventureWorks database and I am creating one User-defined table called “Test_Secondary” on SECONDARY Filegroup.

CREATE TABLE Test_Secondary
Name VARCHAR(10)

So we have created a sample table in the SECONDARY Filegroup and all the other User-defined tables are in the PRIMARY Filegroup which is Default here.

The below query is going to list all the User-Defined tables in all the filegroups in the AdventureWorks Database.

USE AdventureWorks

SELECT DISTINCT   obj.[name],   obj.[type],  fg.[name]
FROM sys.indexes ind
INNER JOIN sys.filegroups fg
ON ind.data_space_id = fg.data_space_id
INNER JOIN sys.tables obj
ON ind.[object_id] = obj.[object_id]
WHERE obj.type = ‘U’ — User Tables

 So we can see all the tables in the PRIMARY Filegroup as well as the table that we created above on SECONDARY filegroup.

Hope this helps..


Subhro Saha