Export (0) Print
Expand All
Expand Minimize

sp_spaceused

SQL Server 2000

  Topic last updated -- January 2004

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

Syntax

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

Arguments

[@objname =] 'objname'

Is the name of the table for which space usage information is requested. objname is nvarchar(776), with a default of NULL.

[@updateusage =] 'updateusage'

Indicates whether or not DBCC UPDATEUSAGE should be run within the database (when no objname is specified) or on a specific object (when objname is specified). Values can be true or false. updateusage is varchar(5), with a default of FALSE.

Return Code Values

0 (success) or 1 (failure)

Result Sets

If objname is omitted, two result sets are returned for the current database.

Column name Data type Description
database_name varchar(128) Name of the current database.
database_size varchar(18) Size of the current database.
unallocated space varchar(18) Space in the database that has not been reserved for any database objects.

Column name Data type Description
reserved varchar(18) Total amount of reserved space.
data varchar(18) Total amount of space used by data.
index_size varchar(18) Total amount of space used by indexes.
unused varchar(18) Total amount of space reserved for objects in the database, but not yet used.

If objname is specified, this is the result set for the specified table.

Column name Data type Description
name nvarchar(128) Name of the table for which space usage information was requested.
rows char(11) Number of rows existing in the objname table.
reserved varchar(18) Total amount of reserved space for objname.
data varchar(18) Total amount of space used by data in objname.
index_size varchar(18) Total amount of space used by indexes in objname.
unused varchar(18) Total amount of space reserved for objname but no yet used.

Remarks

sp_spaceused computes the amount of disk space used for data and indexes, and the disk space used by a table in the current database. If objname is not given, sp_spaceused reports on the space used by the entire current database.

When updateusage is specified, Microsoft® SQL Server™ scans the data pages in the database and makes any necessary corrections to the sysindexes table regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the sysindexes information for the table may not be current. This process can take some time to run on large tables or databases. Use it only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.

Permissions

Execute permissions default to the public role. Only members of the sysadmin fixed server role or the db_owner fixed database role can specify the @updateusage parameter.

Examples
A. Space information about a table

This example reports the amount of space allocated (reserved) for the titles table, the amount used for data, the amount used for index(es), and the unused space reserved by database objects.

USE pubs
EXEC sp_spaceused 'titles'
B. Updated space information about a complete database

This example summarizes space used in the current database and uses the optional parameter @updateusage.

USE pubs
sp_spaceused @updateusage = 'TRUE'

See Also

CREATE INDEX

CREATE TABLE

DBCC SQLPERF

DROP INDEX

DROP TABLE

sp_help

sp_helpindex

System Stored Procedures

Show:
© 2014 Microsoft