About Me ·
At GROSSWEBER we practice what we preach. We offer trainings for modern software technologies like Behavior Driven Development, Clean Code and Git. Our staff is fluent in a variety of languages, including English.
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 Thursday, April 17, 2014 11:45:06 PM (W. Europe Daylight Time, UTC+02:00)
newtelligence dasBlog 2.3.257.0
© Copyright 2014, admin
The opinions expressed herein are my own personal opinions and do not represent
my employer's view in any way.