Retrieving Table Size in SQL Server

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

USE 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).

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: