July 2010 Tip of the Month

By SQLRx Admin | Helpful Scripts

Aug 06

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

>