About Me · Send mail to the author(s) E-mail · Twitter

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.

Feed Icon


Open Source Projects


Blogs of friends

Now playing [?]

Error retrieving information from external service.

How To Obtain The Size Of All Tables In A SQL Server Database

Posted in SQL Server at Monday, 19 November 2007 14:08 W. Europe Standard Time

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.

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.



-- 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 ''?'''


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


The screen shot below shows the results of a smaller testing database.

Table Sizes

Now Playing [?]: TerranovaDigital TendernessCommon places

Monday, 13 October 2008 05:55:45 (W. Europe Daylight Time, UTC+02:00)
This is a truly wonderful query.. and I found it very useful.

However, I have commented the line: DBCC UPDATEUSAGE(0) to make sure that my production database is not hung by this possibly a long run of this.

It worked very well even without the above line.

Thank you. I hope some others will find it useful too.

Fiaz Idris
Friday, 17 October 2008 11:59:00 (W. Europe Daylight Time, UTC+02:00)
Thak you. Very usefull. MB
Milan Brincil
Tuesday, 27 October 2009 07:43:54 (W. Europe Standard Time, UTC+01:00)
very nice tnx.
Thursday, 25 March 2010 23:59:26 (W. Europe Standard Time, UTC+01:00)
This has been very helpful. This was exactly what I was looking for. Thanks!
JP Babiera
Saturday, 01 May 2010 01:17:02 (W. Europe Daylight Time, UTC+02:00)
Thank you!
Thursday, 17 March 2011 19:32:10 (W. Europe Standard Time, UTC+01:00)
Hi buddy.

I am new in sqlserver, I was working with Oracle for long time. This posts is excellent. pls is possible send me other basic tips for the
admin. for example DB Sizes, Data Files, User, Tables. etc.


Tuesday, 29 March 2011 14:42:10 (W. Europe Daylight Time, UTC+02:00)
some sql experience. tasked with migrating a major mainframe database to sql. used databridge from attachmate. it generated sql from mainframe ddl. this is only for the "raw" sql db. tbls will match mainframe on 1st migration(571 mainframe tbls become 1017 sql tbls-cobol type occurs, varible format records etc). 2nd step in migration process will be to normalize the raw sql db.

question is: have the existing mainframe rows counts and max rows, but the sql db is empty. Is there a method/tool that can read the sql schema on an empty db, but can process externally supplied row counts - exiting and proposed?
Dave S
Tuesday, 29 March 2011 15:08:28 (W. Europe Daylight Time, UTC+02:00)

I am by no means a SQL expert, but I suggest you take a look at SQL Server Integration Services to do your migration.


All comments require the approval of the site owner before being displayed.
(will show your gravatar icon)
[Captcha]Enter the code shown (prevents robots):

Live Comment Preview