Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
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
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap