Find space occupied by all tables and indexes

Here's a quick way to find the space occupied by all tables and indexes in a SQL Server 2000 database.

Here is a quick way to find the space occupied by all tables and indexes in a SQL Server 2000 database. This is

an invaluable tool for DBAs for capacity planning.

Just execute the following script in the database you want to check statistics for:

sp_msforeachtable 'sp_spaceused "?"'
The script makes use of the undocumented Microsoft stored procedure sp_msforeachtable that executes the same command for every table in the database. The "?" serves as a placeholder for the table name and sp_spaceused is a built-in stored procedure that displays the space used by a table.

There is another very useful undocumented Microsoft stored procedure called sp_msforeachdb that will perform the same command for every database on the server. You can run a DBCC CHECKDB command on all databases with this simple script:

EXEC sp_Msforeachdb "DBCC checkdb ('?')"  

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free SQL Server tips and scripts.
  • Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.

This was first published in November 2003

Dig deeper on SQL Server Stored Procedures

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close