Finding Tables Sizes with File Group information

Posted: November 26, 2014 in dbUtilities, Scripts, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014

Sometimes managing multiple file groups and the amount of data that is in them can cause a few logistic issues. Recently I found myself needing to know what tables are in what file groups and what the sizes of those tables are. With the database the size it is and the number of objects that exist in it, I needed a script. The goal of this script is only to define the tables, and the sizes of the tables/indexes and what file group the tables are on (not the location of indexes). Consider it a quick reference. I have not tested it against partitioned tables yet however I plan to in the future.

To get the below script I started a profiler trace, and then executed the standard “Disk Usage By Table” report. After I found the query that was executed by the report I copied it and then added some basic file group data collecting to it.

SELECT
       --(row_number() over(order by a3.name, a2.name))%2 as l1,
       a3.name AS [schemaname],
       a2.name AS [tablename],
       a5.FileGroup as [filegroup],
       a1.rows as row_count,
       (a1.reserved + ISNULL(a4.reserved,0))* 8 /1024 AS 'reserved (mb)',
       a1.data * 8 /1024 AS 'data (mb)',
       (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 /1024 AS 'index_size (mb)',
       (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024 AS 'unused (mb)'
FROM
       (SELECT
              ps.object_id,
              SUM (
                     CASE
                           WHEN (ps.index_id < 2) THEN row_count
                           ELSE 0
                     END
                     ) AS [rows],
              SUM (ps.reserved_page_count) AS reserved,
              SUM (
                     CASE
                           WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                           ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
                     END
                     ) AS data,
              SUM (ps.used_page_count) AS used
       FROM sys.dm_db_partition_stats ps
       GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
       (SELECT
              it.parent_id,
              SUM(ps.reserved_page_count) AS reserved,
              SUM(ps.used_page_count) AS used
       FROM sys.dm_db_partition_stats ps
       INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
       WHERE it.internal_type IN (202,204)
       GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id)
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
Join
       (
       Select groupid, data_space_id, indx.id, ds.name as 'FileGroup', ob.name as 'tblName'
       from sysindexes indx
       join sys.data_spaces ds ON indx.groupid = ds.data_space_id
       join sysobjects ob ON [ob].[id] = [indx].[id]
       where indx.indid = 1 or indx.indid = 0
       and ob.type = 'u'
       ) AS a5 on a2.name = a5.tblName
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY a3.name, a2.name
Comments
  1. Andrew J. Kelly says:

    Chris, This query uses some outdated compatibility views in the last join which filters out some tables from the final result set. If you replace that last section with this it should be good to go for later versions of SQL Server.
    Join
    (
    SELECT indx.data_space_id, indx.object_id, ds.name AS ‘FileGroup’, ob.name AS ‘tblName’, ob.type
    FROM sys.indexes AS indx
    INNER JOIN sys.data_spaces AS ds ON indx.data_space_id = ds.data_space_id
    INNER JOIN sys.objects AS ob ON [ob].[object_id] = [indx].[object_id]
    WHERE (indx.index_id IN (1,0)) and ob.[type] = ‘U’
    ) AS a5 on a2.name = a5.tblName

    Andy Kelly

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