In a current project we were wondering why an application database grew quickly during the last couple of weeks of development and testing. We wanted to know the space used of each table without using Management Studio's table properties.
Actually SQL Server gives you everything you need with 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) -- 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
The screen shot below shows the results of a smaller testing database.
Now Playing [?]: Terranova – Digital Tenderness – Common places
a@href@title, blockquote@cite, em, strike, strong, sub, sup, u
Page rendered at Tuesday, May 21, 2013 12:39:57 AM (W. Europe Daylight Time, UTC+02:00)
newtelligence dasBlog 2.3.257.0
© Copyright 2013, admin
The opinions expressed herein are my own personal opinions and do not represent
my employer's view in any way.