RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL
Expertise: Intermediate
Nov 18, 2009

Retrieving Table Size in SQL Server

The following SQL query returns the size of each table in the specified database.

USE <db_name>

sp_MSforeachtable @command1='EXEC sp_spaceused ''?''',@whereand='or OBJECTPROPERTY(o.id, N''IsSystemTable'') = 1'

Here's how it works. SQL Server's system stored procedure sp_MSforeachtable lets you execute code against every table without having to write cursors or while loops. The procedure enumerates through each table in a database and executes a command that you specify as a parameter.

I found this code very useful for estimating the size and growth of a database over time. The query works in all modern versions of SQL Server (i.e., 2000/2005/2008).

Deepak Choudhari
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date