Space used in SQL for all tables – the most wonderful query I’ve seen

by Xavier Comments: 0

So I saw this today in my control panel… I am over my sql quota in a small project…

Crap….

However, I found this query to find all space used in a sql database. It leverages sp_spaceused but builds a table that you can quickly review. BTW: I didn’t create it, I just borrowed it from here http://therightstuff.de/CommentView,guid,df930155-f60f-4f56-ab33-f1352ff091a1.aspx

Big thank you to Mr Alexander, whom I’ve never met in my life but such a nice query is appreciated on a Saturday night.

SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT *
FROM #t

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

DROP TABLE #t

Oh, and forgot to say. I deleted logs and unnecessary stuff. Shrink db and now I am well under my quota. Hoorray! Saved some money!