sp_spaceused

Syntax

sp_spaceused [objname [,1] ]

Parameters

objname – is the name of the table on which to report. If omitted, a summary of space used in the current database appears.
1 – prints separate information on the table’s indexes and text/image storage.

Examples

Example 1 – Reports on the amount of space allocated (reserved) for the titles table, the amount used for data, the amount used for index(es), and the available (unused) space:

sp_spaceused titles

name       rowtotal   reserved   data    index_size   unused
---------- ---------  ---------  ------- ----------   ----------
titles     18         46 KB      6 KB    4 KB         36 KB

Example 2 – In addition to information on the titles table, prints information for each index on the table:

sp_spaceused titles, 1

index_name           size       reserved   unused
 -------------------- ---------- ---------- ---------- 
 titleidind           2 KB       32 KB      24 KB      
 titleind             2 KB       16 KB      14 KB      
     
 name       rowtotal   reserved   data   index_size unused
---------- --------  ---------  ------- ---------- ----------
titles           18      46 KB     6 KB       4 KB      36 KB

Example 3 – Displays the space taken up by the text/image page storage separately from the space used by the table. The object name for text/image storage is “t” plus the table name:

sp_spaceused blurbs,1

index_name           size       reserved   unused     
-------------------- ---------- ---------- ---------- 
blurbs               0 KB       14 KB      12 KB
tblurbs              14 KB      16 KB      2 KB

name       rowtotal reserved    data    index_size unused
---------- -------- ----------- ------- ---------- ---------- 
blurbs            6       30 KB    2 KB      14 KB      14 KB

Example 4 – Prints a summary of space used in the current database:

sp_spaceused

database_name   database_size 
--------------- --------------------------- 
master          5 MB 

reserved    data        index_size  unused 
---------   ---------   ----------- --------
2176 KB     1374 KB     72 KB       730 KB

Example 5 – Reports on the amount of space reserved and the amount of space available for the transaction log:

sp_spaceused syslogs

name       rowtotal   reserved   data    index_size   unused
---------- ---------  ---------  ------- ----------   ----------
syslogs    Not avail. 32 KB     32 KB   0 KB         0 KB

Usage

There are additional considerations when using sp_spaceused:

sp_spaceused displays estimates of the number of data pages, space used by a specified table or by all tables in the current database, and the number of rows in the tables. sp_spaceused computes the rowtotal value using the rowcnt built-in function. This function uses a value for the average number of rows per data page based on a value in the allocation pages for the object. This method is very fast, but the results are estimates, and update and insert activity change actual values. The update statistics command, dbcc checktable, and dbcc checkdb update the rows-per-page estimate, so rowtotal is most accurate after one of these commands executes. Always use select count(*) if you need exact row counts.
sp_spaceused reports on the amount of space affected by tables, clustered indexes, and nonclustered indexes.
The amount of space allocated (reserved) reported by sp_spaceused is a total of the data, index size, and available (unused) space.
Space used by text and image columns, which are stored as separate database objects, is reported separately in the index_size column and is included in the summary line for a table. The object name for text/image storage in the index_size column is “t” plus the table name.
When used on syslogs, sp_spaceused reports rowtotal as “Not available”. See Example 5.

See also create index, create table, drop index, drop table in Reference Manual: Commands.

Permissions

Any user can execute sp_spaceused. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect