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.
sp_spaceused [[@objname =] 'objname']
[,[@updateusage =] 'updateusage']
0 (success) or 1 (failure)
If objname is omitted, two result sets are returned.
Column name | Data type | Description |
---|---|---|
database_name | varchar(18) | Name of the current database |
database_size | varchar(18) | Size of the current database |
unallocated space | varchar(18) | Unallocated space for the database |
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) | Space used by indexes |
unused | varchar(18) | Amount of unused space |
If parameters are specified, this is the result set.
Column name | Data type | Description |
---|---|---|
name | nvarchar(20) | 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) | Amount of total reserved space for objname |
data | varchar(18) | Amount of space used by data in objname |
index_size | varchar(18) | Amount of space used by the index in objname |
unused | varchar(18) | Amount of unused space in objname |
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.
Execute permissions default to the public role.
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'
This example summarizes space used in the current database and uses the optional parameter @updateusage.
USE pubs
sp_spaceused @updateusage = 'TRUE'
Execute permissions default to the public role.
CREATE INDEX | DROP TABLE |
CREATE TABLE | sp_help |
DBCC SQLPERF | sp_helpindex |
DROP INDEX | System Stored Procedures |