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.
sp_spaceused [objname [,1] ]
sp_spaceused titles
name rowtotal reserved data index_size unused ---------- --------- --------- ------- ---------- ---------- titles 18 46 KB 6 KB 4 KB 36 KB
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
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
sp_spaceused
database_name database_size --------------- --------------------------- master 5 MB reserved data index_size unused --------- --------- ----------- -------- 2176 KB 1374 KB 72 KB 730 KB
sp_spaceused syslogs
name rowtotal reserved data index_size unused ---------- --------- --------- ------- ---------- ---------- syslogs Not avail. 32 KB 32 KB 0 KB 0 KB
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.
Any user can execute sp_spaceused. Permission checks do not differ based on the granular permissions settings.
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 |
|