Contact

admin

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

Tags

Open Source Projects

Archives

Blogs of friends

Now playing [?]

  1. Marius Müller-Westernhagen – Willenlos
  2. Tiamat – Love In Chains
  3. Tiamat – Wings Of Heaven
  4. Tiamat – Too Far Gone
  5. Tiamat – I Am In Love With Myself
Audioscrobbler/Last.fm

ClustrMap

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

Posted in SQL Server at Monday, November 19, 2007 2:08 PM 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.

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.

Table Sizes

Now Playing [?]: TerranovaDigital TendernessCommon places

Monday, October 13, 2008 5:55:45 AM (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
Fiaz Idris
Friday, October 17, 2008 11:59:00 AM (W. Europe Daylight Time, UTC+02:00)
Thak you. Very usefull. MB
Milan Brincil
Tuesday, October 27, 2009 7:43:54 AM (W. Europe Standard Time, UTC+01:00)
very nice tnx.
bidel
Thursday, March 25, 2010 11:59:26 PM (W. Europe Standard Time, UTC+01:00)
This has been very helpful. This was exactly what I was looking for. Thanks!
JP Babiera
Saturday, May 01, 2010 1:17:02 AM (W. Europe Daylight Time, UTC+02:00)
Thank you!
Aleb
Thursday, March 17, 2011 7:32:10 PM (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.

Thnks.

Regards,
hrz.marco@gmail.com
Mark
Tuesday, March 29, 2011 2:42:10 PM (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, March 29, 2011 3:08:28 PM (W. Europe Daylight Time, UTC+02:00)
Dave,

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

HTH,

Alex
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