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 (http://blog.SQLAuthority.com)

About these ads

33 thoughts on “SQL SERVER – List All Objects Created on All Filegroups in Database

  1. Hello Pinal,
    When do we create a File Group?

    In our prod environment we have a db with 3 datafiles and 1 log file,
    All the 3 dbs have the same size 16 gb and the log file is 5 gb.
    They are all on the PRIMARY file group.
    All the 4 files are on four different drives.

    I am just wondering whether creating File Groups here will help?

    Thanks
    Biju

  2. Hello Sir,

    It’s very nice article and i try to find about the
    how i can get file groups in primary key it is very use ful
    to create script for primary key.

    Thnaks,
    Kuldip Bhatt

  3. What about statistics? What I mean is before SQL 2008/2005 we had groupid for sysindexes, which also contained stats info.

    Now there’s sys.indexes and sys.stats. sys.indexes has data_space_id, sys.stats does not. How is this handled in SQL 2008 then?

    Thanks for your help…

  4. This is a very handy article but I see one flaw. It presumes that your object has an index. How would this be done if the object doesn’t have an index?

  5. If you have partitioned tables and you use this queries you cant see the that some partitions reside at some filegroups. You think that there’s no objects in a filegroup but there are lots of partitions.

    For instance: I have 4 filegroups and none table created in one specific filegroup but in some partition schemes that use the four filegroups. When you query sys.indexes and sys.filegroups none objects are displayed.

  6. Pinal,

    I am working on file group audit and saw the same script you are using at mssqltips.com (http://www.mssqltips.com/tip.asp?tip=1112)
    But I also want to find the view, procedures and functions on the filegroup. I could not find a way to do that. I will appreciate if you can help me with that.

    Your community spirit is unparalleled. I like to come to your blog when I have problem at my work to look for solution and many a time I have found one.

  7. Hi Pinal,

    Writtten this SP to find the large tables in the filegroup. Handy when you need to move large tables to other filegroups.

    CREATE PROC sp_show_huge_tables
    (
    @top int = NULL,
    @include_system_tables bit = 0
    )
    AS
    /*************************************************************************************************

    Purpose: To list the size of all tables in the database in descending order (that is biggere tables first).
    Output can be restricted to top n tables. That is you can pass the value 3 to @top parameter to
    see the top 3 biggest tables in your database. Optionally, you can use @include_system_tables
    parameter, to include systemt tables in the output.
    NOTE: Always create this procedure in the master database and call it from the required databases,
    as if the stored procedure is created in that database (That is, don’t prefix the stored procedure
    with the database name).

    Written by: John Saldanha

    Tested on: SQL Server 10.0

    Date created: October – 4 -2010 2:00:00 PM IST

    Date modified: October – 4 -2010 2:00:00 PM IST

    Email: john.saldanha@yahoo.co.uk

    Examples:

    To list all the user tables in the database along with their sizes:
    EXEC sp_show_huge_tables

    To see the top three biggest tables in your database:
    EXEC sp_show_huge_tables 3

    To list all the user AND system tables in the database along with their sizes:
    EXEC sp_show_huge_tables @include_system_tables = 1

    To see the top three biggest user or system tables in your database:
    EXEC sp_show_huge_tables 3, 1
    *************************************************************************************************/

    BEGIN
    IF @top > 0
    SET ROWCOUNT @top

    SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)], FileGroup FROM
    (
    SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS [Table Name],
    CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024.)/1024.)) AS [Total space used (MB)], f.name As FileGroup
    FROM sys.sysindexes i (NOLOCK)
    INNER JOIN
    sysobjects o (NOLOCK)
    ON
    i.id = o.id AND
    ((@include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U') AND
    ((@include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
    INNER JOIN sys.filegroups f
    ON i.groupid = f.data_space_id
    WHERE indid IN (0, 1, 255)
    AND i.groupid = f.data_space_id
    GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)), f.name

    ) as a
    ORDER BY [Total space used (MB)] DESC

    SET ROWCOUNT 0
    END

    GO

  8. Hi Pinal,

    Great post.

    It would be nice to have the size of the table/index as well along with the file group in which it is stored. The administrator can then concentrate only on tables/indexes with large size.

  9. Hi…

    I hope everything will go smoothly. and wanted to know if anyone knows how I can define the GOTO instruction and that taken as a parameter eg @ GoTo Label
    Escuchar
    Leer fonéticamente
    Diccionario – Ver diccionario detallado

  10. I hope everything will go smoothly. and wanted to know if anyone knows how I can define the GOTO instruction and that taken as a parameter eg: GoTo @Label

    Thks

  11. Thanks for the post!

    I needed to relate tables to files so I added sys.master_files:

    Select Distinct
    O.name As TableName
    ,I.name As IndexName
    ,I.type_desc As IndexType
    ,I.index_id As IndexID
    ,FG.name As FileGroupName
    ,F.name As FileLogicalName
    ,F.physical_name As FilePhysicalName
    From
    sys.indexes As I
    Inner Join
    sys.filegroups As FG
    On I.data_space_id = FG.data_space_id
    Inner Join
    sys.master_files As F
    On FG.data_space_id = F.data_space_id
    And F.database_id = DB_ID()
    Inner Join
    sys.all_objects As O
    On I.[object_id] = O.[object_id]
    Where
    I.data_space_id = FG.data_space_id
    And O.[type] = ‘U’
    Order By
    TableName
    ,IndexID
    Go

  12. Hi Pinal,
    What is the main impact on performance if a table have 1000k Rows and more than 100 indexes on it.
    How much max indexes are sufficient in a large table.
    I know if the size of the table increases it decreases the performance as well. However we have a high performance database server.

    Thanks
    Shyam

  13. Hi Pinal,
    In both your queries given above, you have the following conditions:

    1. ON i.data_space_id = f.data_space_id
    2. WHERE i.data_space_id = f.data_space_id

    However, the Where clause looks duplicated since the views have already been joined. Please let me know your thoughts on this.

    Cheers,
    Shankar

  14. Hi Pinal. I have a partitioned table with 4 filegroups. Each filegroup has two data files. SQL server is only writing to one data file of each files group. The second datafile for every filegroup is empty. I load 30 million rows a day. Before I log a call to Microsoft, do you have any ideas

    Many Thanks
    Mohamed

  15. 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.

  16. 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.

  17. 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’

  18. 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.

  19. 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

  20. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

  21. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s