How to find the size of all tables in SQL Server database

SQL Server gives you everything its stored procedure sp_spaceused. Unfortunately this SP does not support iterating over all tables in a database, so we needed to leverage another (undocumented) Stored Procedure sp_msForEachTable.

SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- Find DB size.
EXEC sp_spaceused

-- Create a table to counts row and sizes.
CREATE TABLE #tbl
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

INSERT #tbl EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT * FROM #tbl

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM #tbl

DROP TABLE #tbl

After executing this, you will get the size of all tables in your database.

Please share, if it is helpfulShare on FacebookShare on LinkedInTweet about this on TwitterShare on Google+Email this to someonePrint this page

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>