Quick Tip – How much Space a Table has used in SQL Server

Space used by a single Table

One of the easiest ways to know how much space a table has used is by using sp_spaceused procedure.

sp_spaceused 'dbo.WordMeaning';

Or

Exec sp_spaceused 'dbo.WordMeaning';

The result may look like this.

Image

See the column named data. It shows the space used by the table named WordMeaning.

The system stored procedure sp_spaceused returns a few interesting data about a table or an object.

Along with disk space, the procedure also returns,

• the number of rows used by the table
• space reserved
• the size of index or the total space used by indexes
• and total number of space reserved by a table (or an object) but not used yet.

This built-in procedure is also available in previous SQL Server versions like 2008 etc.

Space used by All Tables in a Database

You can also find the space used by all the tables in a database using sp_spaceused procedure. However, its slightly different from the 1st example (above).

Since there are one than one table in the database, I’ll store the tables in a temporary table and then execute the procedure to get the spaces used by each table.

SET NOCOUNT ON

-- Create a temp table.
CREATE TABLE dbo.temp (
    table_name VARCHAR(100), 
    total_rows INT, 
    space_reseved VARCHAR (20),
    space_occupied VARCHAR (20),
    size_of_index VARCHAR (20),
    unused_space VARCHAR (20) 
)

INSERT INTO dbo.temp
EXEC sp_MSforeachtable @command1=N'EXEC sp_spaceused [?]';
SELECT *FROM dbo.temp ORDER BY table_name

DROP TABLE dbo.temp		-- drop the temporary table.

In the above query, I am creating a temporary table named temp where I can store each table name with other details.

Next, I am using sp_MSforeachtable on sp_spaceused procedure to retrieve number of rows, disk space used etc. for all the tables.

The sp_MSforeachtable stored procedure is an un-documented procedure. That is, this procedure (like many other system procedures) has no mention in MSDN. Its not documented.

When you run this procedure like this,

EXEC sp_MSforeachtable

It will throw an error stating, it expects parameter @command1.

Image

It takes few arguments and “@command1” is one the arguments. The value for @command1 in this case is sp_spaceused procedure.

Here’s another example using sp_MSforeachtable procedure.

Use PMS
EXEC sp_MSforeachtable 'PRINT "?"'

It shows all the tables in the database "PMS"

Use SSMS Report to know how much space is used by Tables

SSMS stands for SQL Server Management Studio.

In case you don’t want to use T-SQL command, no problem. There’s another way. You can use a built-in report from your SQL Server Management Studio.

Just follow these steps.

1) Open SQL Server Management Studio

2) Right click the database and choose Reports -> Standard Reports
You will see a list of options.

3) Finally, choose the option Disk Usage by Table.

Image

← PreviousNext →