SQL Server sp_spaceused Usage

sp_spaceused
Displays the number of rows, reserved disk space, and disk space used by tables in the current database, or displays the disk space reserved and used by the entire database.

Grammar
sp_spaceused [[@objname =] ' objname ']
[, [@updateusage =] ' updateusage ']

Parameters
[@objname =] ' ObjName '

is the name of the table for which the Space usage information (reserved and allocated space) is requested. The objname data type is nvarchar (776), which is set to NULL by default.

[@updateusage =] ' Updateusage '

Indicates that DBCC updateusage should be run within the database (when objname is not specified) or on a specific object (when objname is specified). The value can be true or false. The data type of the updateusage is varchar (5), which is set to FALSE by default.

Return code value
0 (Success) or 1 (failed)

Example
A. Spatial information about the table
The following example reports the amount of space allocated (reserved) for a titles table, the amount of space used by the data, the amount of space used by the index, and the amount of unused space reserved by the database object.

Use XXX
EXEC sp_spaceused XXX

B. Updated spatial information about the entire database
The following example summarizes the space used by the current database and @updateusage with optional parameters.

Use XXX
sp_spaceused @updateusage = ' TRUE '

To view all table sizes for a database at once:
CREATE TABLE Test (
Name varchar (50),
Rows int,
Reserved varchar (20),
Data varchar (20),
Index_size varchar (20),
Unused varchar (20)
)

INSERT INTO Test
exec sp_msforeachtable "exec sp_spaceused '?"
SELECT * FROM Test order by cast (replace (reserved, ' KB ', ') as int) desc

Top

0

SQL Server sp_spaceused Usage