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] ]
is the name of the table on which to report. If omitted, a summary of space used in the current database appears.
prints separate information on the table’s indexes and text/image storage.
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
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
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
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
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
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.
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:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
Catalog stored procedures sp_statistics
Commands create index, create table, drop index, drop table
System procedures sp_helpindex
