Get size of all tables in database

We use table partitioning and had some trouble with the queries provided above due to duplicate records.

For those who need this, you can find below the query as run by SQL Server 2014 when generating the "Disk usage by table" report. I assume it also works with previous versions of SQL Server.

It works like a charm.

SELECT
a2.name AS [tablename],
a1.rows as row_count,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.data * 8 AS data,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
(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
WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
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 <> N'S' and a2.type <> N'IT'
ORDER BY a3.name, a2.name

From a command prompt using [OSQL][1]:

OSQL -E -d <*databasename*> -Q "exec sp_msforeachtable 'sp_spaceused [?]'" > result.txt


[1]:

[To see links please register here]

exec sp_spaceused N'dbo.MyTable'

For all tables ,use..(adding from the comments of Paul)

exec sp_MSForEachTable 'exec sp_spaceused [?]'

Here is a way to get all tables' sizes quickly with the following steps:

1. Write the given **T-SQL commands** to list all database tables:

select 'exec sp_spaceused ' + TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'

2. Now copy the list of database tables, and copy it into a new query analyzer window

exec sp_spaceused table1
exec sp_spaceused table2
exec sp_spaceused table3
exec sp_spaceused table4
exec sp_spaceused table5

3. In SQL **query analyzer**, select from top tool bar option **Results to file** (<kbd>Ctrl</kbd> + <kbd>Shift</kbd> + <kbd>F</kbd>).

4. Now finally hit the **Execute** button red marked from the above **tool bar**.

5. The Database **size of all tables** is now stored in a file on your computer.

[![Enter image description here][1]][1]

[1]:

Here is another method: using *SQL Server Management Studio*, in **Object Explorer**, go to your database and select **Tables**

[![enter image description here][1]][1]


Then open the **Object Explorer Details** (either by pressing **F7** or going to *View->Object Explorer Details*). In the object explorer details page, right click on the column header and enable the columns that you would like to see in the page. You can sort the data by any column too.

[![enter image description here][2]][2]


[1]:

[2]:

Extension to @xav [answer][1] that handled table partitions to get size in MB and GB.
Tested on SQL Server 2008/2012 (Commented a line where `is_memory_optimized = 1`)

SELECT
a2.name AS TableName,
a1.rows as [RowCount],
--(a1.reserved + ISNULL(a4.reserved,0)) * 8 AS ReservedSize_KB,
--a1.data * 8 AS DataSize_KB,
--(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS IndexSize_KB,
--(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS UnusedSize_KB,
CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_MB,
CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_MB,
CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_MB,
CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_MB,
--'| |' Separator_MB_GB,
CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_GB,
CAST(ROUND(a1.data * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_GB,
CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_GB,
CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_GB
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
--===Remove the following comment for SQL Server 2014+
--WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
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 <> N'S' and a2.type <> N'IT'
--AND a2.name = 'MyTable' --Filter for specific table
--ORDER BY a3.name, a2.name
ORDER BY ReservedSize_MB DESC
[1]:

[To see links please register here]

Here is a sample query to get tables larger than 1GB ordered by size descending.


USE YourDB
GO

DECLARE @Mult float = 8
SET @Mult = @Mult / POWER(2, 20) -- Use POWER(2, 10) for MBs

; WITH CTE AS
(
SELECT
i.object_id,
Rows = MAX(p.rows),
TotalSpaceGB = ROUND(SUM(a.total_pages) * @Mult, 0),
UsedSpaceGB = ROUND(SUM(a.used_pages) * @Mult, 0)
FROM
sys.indexes i
JOIN
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
i.object_id > 255
GROUP BY
i.object_id
HAVING
SUM(a.total_pages) * @Mult > 1
)
SELECT
SchemaName = s.name,
TableName = t.name,
c.TotalSpaceGB,
c.UsedSpaceGB,
UnusedSpaceGB = c.TotalSpaceGB - c.UsedSpaceGB,
[RowCount] = c.Rows
FROM
CTE c
JOIN
sys.tables t ON t.object_id = c.object_id
JOIN
sys.schemas s ON t.schema_id = s.schema_id
ORDER BY
c.TotalSpaceGB DESC

As a simple extension to [marc_s's answer][1] (the one that has been accepted), this is adjusted to return column count and allow for filtering:

SELECT *
FROM
(

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
COUNT(DISTINCT c.COLUMN_NAME) as ColumnCount,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
(SUM(a.used_pages) * 8) AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN
INFORMATION_SCHEMA.COLUMNS c ON t.NAME = c.TABLE_NAME
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
) AS Result

WHERE
RowCounts > 1000
AND ColumnCount > 10
ORDER BY
UsedSpaceKB DESC


[1]:

[To see links please register here]

Above queries are good for finding the amount of space used by the table (indexes included), but if you want to compare how much space is used by indexes on the table use this query:

SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM
sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE
i.is_primary_key = 0 -- fix for size discrepancy
GROUP BY
i.OBJECT_ID,
i.index_id,
i.name
ORDER BY
OBJECT_NAME(i.OBJECT_ID),
i.index_id

I found this query to be easy to use & quick.

select schema_name(tab.schema_id) + '.' + tab.name as [table],
cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
from sys.tables (nolock) tab
inner join sys.indexes (nolock) ind
on tab.object_id = ind.object_id
inner join sys.partitions (nolock) part
on ind.object_id = part.object_id and ind.index_id = part.index_id
inner join sys.allocation_units (nolock) spc
on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(spc.used_pages) desc