MICROSOFT SQL SERVER
Find space occupied by all tables and indexes
Parthasarathy Mandayam
11.04.2004
Rating: --- (out of 5)
|
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.
|
|
|
|
|
|
