July 2010 Tip of the Month

SQL Server Administration: List the row count and space used for data and indexes for each table in your database with the following query. This is the same query that is run for the Disk Usage by Table report in Management Studio. Databases that are in 80 or below compatibility cannot use the report but you can still find out usage with the query.

SELECT

 DB_NAME() AS [DatabaseName],

 a3.name AS [SchemaName],

 a2.name AS [TableName],

 a1.rows AS [RowCount],

 (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,

 a1.data * 8 AS [DataKB],

 (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN

   (a1.used + ISNULL(a4.used,0)) – a1.data ELSE 0 END) * 8 AS [IndexKB],

 (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN

   (a1.reserved + ISNULL(a4.reserved,0)) – a1.used ELSE 0 END) * 8 AS unused

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)

WHERE a2.type <> ‘S’ and a2.type <> ‘IT’

ORDER BY a3.name, a2.name

Both comments and trackbacks are currently closed.
%d bloggers like this: