Archive for the ‘Scripts’ Category

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