sp_spaceused

Description

Displays estimates of the number of rows, the number of data pages, the size of indexes, and the space used by a specified table or by all tables in the current database.

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

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:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • 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

See also

Catalog stored procedures sp_statistics

Commands create index, create table, drop index, drop table

System procedures sp_helpindex