Caveman's Blog

My commitment to learning.

SQL Server: Size of a Table and Database

leave a comment »


We can use sp_spaceused, a system stored procedure, to know the size of a table or a database. Here is a sample usage on a table. sp_spaceused displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

sp_spaceused 'Employee'

We can use sp_msForEachTable in combination with sp_spaceused to iterate over all the tables and list the corresponding size of each table of a given database. The sample query lists the attributes of the 2 tables that exist in my sample database.

EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

The following example summarizes space used in the current database and uses the optional parameter @updateusage to ensure current values are returned for the Demo database.

EXEC sp_spaceused @updateusage = N'TRUE';
GO

References:
1. MSDN Online

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: