Problem solve Get help with specific problems with your technologies, process and projects.

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.

Dig Deeper on SQL Server Stored Procedures

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.