Get size of all tables in database

I have inherited a fairly large SQL Server database. It seems to take up more space than I would expect, given the data it contains.

Is there an easy way to determine how much space on disk each table is consuming?

27 Answers

SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows, SUM(a.total_pages) * 8 AS TotalSpaceKB, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB, SUM(a.used_pages) * 8 AS UsedSpaceKB, CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB, CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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
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
ORDER BY TotalSpaceMB DESC, t.Name

If you are using SQL Server Management Studio (SSMS), instead of running a query (which in my case returned duplicate rows) you can run a standard report.

Right click on the database
Navigate to Reports > Standard Reports > Disk Usage By Table

Note: The database compatibility level must be set to 90 or above for this to work correctly. See

sp_spaceused can get you information on the disk space used by a table, indexed view, or the whole database.

For example:

USE MyDatabase; GO
EXEC sp_spaceused N'User.ContactInfo'; GO

This reports the disk usage information for the ContactInfo table.

To use this for all tables at once:

USE MyDatabase; GO
sp_msforeachtable 'EXEC sp_spaceused [?]' GO

You can also get disk usage from within the right-click Standard Reports functionality of SQL Server. To get to this report, navigate from the server object in Object Explorer, move down to the Databases object, and then right-click any database. From the menu that appears, select Reports, then Standard Reports, and then "Disk Usage by Partition: [DatabaseName]".

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

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.

 exec sp_spaceused N'dbo.MyTable'

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

exec sp_MSForEachTable 'exec sp_spaceused [?]'

After some searching, I could not find an easy way to get information on all of the tables. There is a handy stored procedure named sp_spaceused that will return all of the space used by the database. If provided with a table name, it returns the space used by that table. However, the results returned by the stored procedure are not sortable, since the columns are character values.

The following script will generate the information I'm looking for.

create table #TableSize ( Name varchar(255), [rows] int, reserved varchar(255), data varchar(255), index_size varchar(255), unused varchar(255))
create table #ConvertedSizes ( Name varchar(255), [rows] int, reservedKb int, dataKb int, reservedIndexSize int, reservedUnused int)
EXEC sp_MSforeachtable @command1="insert into #TableSize
EXEC sp_spaceused '?'"
insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused)
select name, [rows],
SUBSTRING(reserved, 0, LEN(reserved)-2),
SUBSTRING(data, 0, LEN(data)-2),
SUBSTRING(index_size, 0, LEN(index_size)-2),
SUBSTRING(unused, 0, LEN(unused)-2)
from #TableSize
select * from #ConvertedSizes
order by reservedKb desc
drop table #TableSize
drop table #ConvertedSizes

For Azure I used this:

You should have SSMS v17.x+

I used;

With this, as User Sparrow has mentioned:

Open your Databases> and select Tables,
Then press key F7You should see the row count
as:

SSMS here is connected to Azure databases

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

Marc_s' answer gives incorrect results when working with multiple partitions and/or filtered indexes. It also doesn't distinguish between the size of the data and indexes, which is often very relevant. Several suggested fixes don't address the core problem or are simply wrong as well.

The following query addresses all of those issues.

SELECT [object_id] = t.[object_id] ,[schema_name] = s.[name] ,[table_name] = t.[name] ,[index_name] = CASE WHEN i.[type] in (0,1,5) THEN null ELSE i.[name] END -- 0=Heap; 1=Clustered; 5=Clustered Columnstore ,[object_type] = CASE WHEN i.[type] in (0,1,5) THEN 'TABLE' ELSE 'INDEX' END ,[index_type] = i.[type_desc] ,[partition_count] = p.partition_count ,[row_count] = p.[rows] ,[data_compression] = CASE WHEN p.data_compression_cnt > 1 THEN 'Mixed' ELSE ( SELECT DISTINCT p.data_compression_desc FROM sys.partitions p WHERE i.[object_id] = p.[object_id] AND i.index_id = p.index_id ) END ,[total_space_MB] = cast(round(( au.total_pages * (8/1024.00)), 2) AS DECIMAL(36,2)) ,[used_space_MB] = cast(round(( au.used_pages * (8/1024.00)), 2) AS DECIMAL(36,2)) ,[unused_space_MB] = cast(round(((au.total_pages - au.used_pages) * (8/1024.00)), 2) AS DECIMAL(36,2))
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN ( SELECT [object_id], index_id, partition_count=count(*), [rows]=sum([rows]), data_compression_cnt=count(distinct [data_compression]) FROM sys.partitions GROUP BY [object_id], [index_id]
) p ON i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id]
JOIN ( SELECT p.[object_id], p.[index_id], total_pages = sum(a.total_pages), used_pages = sum(a.used_pages), data_pages=sum(a.data_pages) FROM sys.partitions p JOIN sys.allocation_units a ON p.[partition_id] = a.[container_id] GROUP BY p.[object_id], p.[index_id]
) au ON i.[object_id] = au.[object_id] AND i.[index_id] = au.[index_id]
WHERE t.is_ms_shipped = 0 -- Not a system table

Extension to @xav answer 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

If you need to calculate exactly the same numbers, that are on 'table properties - storage' page in SSMS, you need to count them with the same method as it done in SSMS (works for sql server 2005 and above ... and also works correctly for tables with LOB fields - because just counting "used_pages" is not enought to show accurate index size):

;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE lob_used_page_count + row_overflow_used_page_count END) as pages
FROM sys.dm_db_partition_stats AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select cte.TableName, cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB, cast(((CASE WHEN cte.used_pages_count > cte.pages THEN cte.used_pages_count - cte.pages ELSE 0 END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc

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

A small change from Marc_s's answer, since I have been going back to this page so often, ordered by most row's first:

SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, 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
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
ORDER BY --p.rows DESC --Uncomment to order by amount rows instead of size in KB. SUM(a.total_pages) DESC

-- Show the size of all the tables in a database sort by data size descending
SET NOCOUNT ON
DECLARE @TableInfo TABLE (tablename varchar(255), rowcounts int, reserved varchar(255), DATA varchar(255), index_size varchar(255), unused varchar(255))
DECLARE @cmd1 varchar(500)
SET @cmd1 = 'exec sp_spaceused ''?'''
INSERT INTO @TableInfo (tablename,rowcounts,reserved,DATA,index_size,unused)
EXEC sp_msforeachtable @command1=@cmd1
SELECT * FROM @TableInfo ORDER BY Convert(int,Replace(DATA,' KB','')) DESC

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

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'

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

In SQL query analyzer, select from top tool bar option Results to file (Ctrl + Shift + F).
Now finally hit the Execute button red marked from the above tool bar.
The Database size of all tables is now stored in a file on your computer.

For get all table size in one database you can use this query :

Exec sys.sp_MSforeachtable ' sp_spaceused "?" '

And you can change it to insert all of result into temp table and after that select from temp table.

Insert into #TempTable Exec sys.sp_MSforeachtable ' sp_spaceused "?" '
Select * from #TempTable

This will give you the sizes, and record counts for each table.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- Get a list of tables and their sizes on disk
ALTER PROCEDURE [dbo].[sp_Table_Sizes]
AS
BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
DECLARE @table_name VARCHAR(500)
DECLARE @schema_name VARCHAR(500)
DECLARE @tab1 TABLE( tablename VARCHAR (500) collate database_default ,schemaname VARCHAR(500) collate database_default
)
CREATE TABLE #temp_Table ( tablename sysname ,row_count INT ,reserved VARCHAR(50) collate database_default ,data VARCHAR(50) collate database_default ,index_size VARCHAR(50) collate database_default ,unused VARCHAR(50) collate database_default
)
INSERT INTO @tab1
SELECT Table_Name, Table_Schema
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE c1 CURSOR FOR
SELECT Table_Schema + '.' + Table_Name
FROM information_schema.tables t1
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN c1
FETCH NEXT FROM c1 INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN SET @table_name = REPLACE(@table_name, '[',''); SET @table_name = REPLACE(@table_name, ']',''); -- make sure the object exists before calling sp_spacedused IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name)) BEGIN INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false; END FETCH NEXT FROM c1 INTO @table_name
END
CLOSE c1
DEALLOCATE c1
SELECT t1.* ,t2.schemaname
FROM #temp_Table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,t1.tablename;
DROP TABLE #temp_Table
END

From a command prompt using OSQL:

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

I added a few more columns on top of marc_s answer:

with fs
as
(
select i.object_id, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKb
from sys.indexes i 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
WHERE i.OBJECT_ID > 255
GROUP BY i.object_id, p.rows
)
SELECT t.NAME AS TableName, fs.RowCounts, fs.TotalSpaceKb, t.create_date, t.modify_date, ( select COUNT(1) from sys.columns c where c.object_id = t.object_id ) TotalColumns
FROM sys.tables t INNER JOIN fs ON t.OBJECT_ID = fs.object_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0
ORDER BY t.Name

My post is only relevant for SQL Server 2000 and has been tested to work in my environment.

This code accesses All possible databases of a single instance, not just a single database.

I use two temp tables to help collect the appropriate data and then dump the results into one 'Live' table.

Returned data is: DatabaseName, DatabaseTableName, Rows (in the Table), data (size of the table in KB it would seem), entry data (I find this useful for knowing when I last ran the script).

Downfall to this code is the 'data' field is not stored as an int (The chars 'KB' are kept in that field), and that would be useful (but not totally necessary) for sorting.

Hopefully this code helps someone out there and saves them some time!

CREATE PROCEDURE [dbo].[usp_getAllDBTableSizes]
AS
BEGIN SET NOCOUNT OFF CREATE TABLE #DatabaseTables([dbname] sysname,TableName sysname) CREATE TABLE #AllDatabaseTableSizes(Name sysname,[rows] VARCHAR(18), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18)) DECLARE @SQL nvarchar(4000) SET @SQL='select ''?'' AS [Database], Table_Name from [?].information_schema.tables WHERE TABLE_TYPE = ''BASE TABLE'' ' INSERT INTO #DatabaseTables(DbName, TableName) EXECUTE sp_msforeachdb @Command1=@SQL DECLARE AllDatabaseTables CURSOR LOCAL READ_ONLY FOR SELECT TableName FROM #DatabaseTables DECLARE AllDatabaseNames CURSOR LOCAL READ_ONLY FOR SELECT DBName FROM #DatabaseTables DECLARE @DBName sysname OPEN AllDatabaseNames DECLARE @TName sysname OPEN AllDatabaseTables WHILE 1=1 BEGIN FETCH NEXT FROM AllDatabaseNames INTO @DBName FETCH NEXT FROM AllDatabaseTables INTO @TName IF @@FETCH_STATUS<>0 BREAK INSERT INTO #AllDatabaseTableSizes EXEC ( 'EXEC ' + @DBName + '.dbo.sp_spaceused ' + @TName) END --(v=sql.80).aspx INSERT INTO rsp_DatabaseTableSizes (DatabaseName, name, [rows], data) SELECT [dbname], name, [rows], data FROM #DatabaseTables INNER JOIN #AllDatabaseTableSizes ON #DatabaseTables.TableName = #AllDatabaseTableSizes.Name GROUP BY [dbname] , name, [rows], data ORDER BY [dbname] --To be honest, I have no idea what exact duplicates we are dropping -- but in my case a near enough approach has been good enough. DELETE FROM [rsp_DatabaseTableSizes] WHERE name IN ( SELECT name FROM [rsp_DatabaseTableSizes] GROUP BY name HAVING COUNT(*) > 1 ) DROP TABLE #DatabaseTables DROP TABLE #AllDatabaseTableSizes CLOSE AllDatabaseTables DEALLOCATE AllDatabaseTables CLOSE AllDatabaseNames DEALLOCATE AllDatabaseNames
END
--EXEC [dbo].[usp_getAllDBTableSizes]

In case you need to know, the rsp_DatabaseTableSizes table was created through:

CREATE TABLE [dbo].[rsp_DatabaseSizes]( [DatabaseName] [varchar](1000) NULL, [dbSize] [decimal](15, 2) NULL, [DateUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO

Riffing on @Mark answer above, added the @updateusage='true' to force the latest size stats ():

 SET NOCOUNT ON DECLARE @TableInfo TABLE (tablename varchar(255), rowcounts int, reserved varchar(255), DATA varchar(255), index_size varchar(255), unused varchar(255)) DECLARE @cmd1 varchar(500) SET @cmd1 = 'exec sp_spaceused @objname =''?'', @updateusage =''true'' ' INSERT INTO @TableInfo (tablename,rowcounts,reserved,DATA,index_size,unused) EXEC sp_msforeachtable @command1=@cmd1
SELECT * FROM @TableInfo ORDER BY Convert(int,Replace(DATA,' KB','')) DESC

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 (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

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

IF you only care about the empty wasted space in the database and not the individual tables you can consider the following:

If the database experiences large amount of data insertion and then deletions, maybe like in an ETL situation, this will cause too much unused space in the database as the file groups auto-grows but never auto-shrinks back.

You can see if this is the case by using the Properties page of your database. You can shrink (right click on the database > Tasks > Shrink) and claim some space back. But, if the underlying reason is still there, the database will grow back (and consume extra time trying to grow back and things gets slowed down until it grows enough - so don't do it in that case)

CREATE TABLE #tmp_table_info
(
id int identity(1,1),
tblname varchar(200)
);
CREATE TABLE #SpaceUsed
( TableName sysname ,NumRows BIGINT ,ReservedSpace VARCHAR(50) ,DataSpace VARCHAR(50) ,IndexSize VARCHAR(50) ,UnusedSpace VARCHAR(50)
)
insert into #tmp_table_info
select s.name+'.'+t.name
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
where t.type = 'U';
declare @min int =1,@max int = 0
select @max = count(*)
from #tmp_table_info
while(@min<=@max)
begin declare @tablename varchar(200) select @tablename=tblname from #tmp_table_info where id =@min DECLARE @str VARCHAR(500) SET @str = 'sp_spaceused '''+@tablename+'''' INSERT INTO #SpaceUsed EXEC (@str) set @min =@min + 1
end;
select @@SERVERNAME as servername,DB_NAME() as DatabaseName,CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024 as ReservedSpace_MB,
CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024 as DataSpace_MB,
CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024 as IndexSpace_MB,
CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024 as UnusedSpace_MB from #SpaceUsed
drop table #tmp_table_info
drop table #SpaceUsed

Maybe the Tables have a more partion file & must show the file order

SELECT T1.Name AS TableName, T5.Name AS SchemaName, T3.partition_number AS PartionNumber, T3.Rows AS RowsCount, SUM(T4.total_pages) * 8 AS TotalSpaceKB, SUM(T4.used_pages) * 8 AS UsedSpaceKB, (SUM(T4.total_pages) - SUM(T4.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.objects T1 INNER JOIN sys.indexes T2 ON T1.object_id = T2.object_id INNER JOIN sys.partitions T3 ON T2.object_id = T3.object_id AND T2.index_id = T3.index_id INNER JOIN sys.allocation_units T4 ON T3.partition_id = T4.container_id LEFT JOIN sys.schemas T5 ON T1.schema_id = T5.schema_id
WHERE T1.type='U'
GROUP BY T1.Name, T5.Name, T3.Rows,T3.partition_number
ORDER BY T1.Name,T3.partition_number;

Hype Blast

Get size of all tables in database

27 Answers

You Might Also Like

"Here’s what’s going on with Twitter and Substack. Twitter has monopoly mindshare among elite media, and that’s a key part of the audience that Substack is trying to reach. Musk believes that Substack is a potential rival, so he’s trying to maintain his market power by preventing…"

Review: Survivor, "This Is Where The Legends Are Made"

Robert Reich | Substack

How do you beat Endogeny?