sp_spaceused

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

Examples

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:

InformationValues
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

Related reference
sp_helpindex
sp_statistics