sp_spaceused
Display line numbers, reserved disk space, and disk space used by tables in the current database, or displays the disk space reserved and used by the entire database.
grammar
sp_spaceused [[@objname =] 'ObjName']
[, [@ UpdateUsage =] 'updateusage']
parameter
[@ObjName =] 'ObjName'
It is a table name for requesting space usage information (reserved and allocated space). The data type of ObjName is NVARCHAR (776), and the default is set to NULL.
[@updateusage =] 'updateUsage'
Indicates that DBCC UpdateUsage should be run on the database (when not specified OBJNAME) or on a specific object (specifying OBJNAME). The value can be True or False. UpdateUsage's data type is VARCHAR (5), the default is set to false.
Returns the code value
0 (success) or 1 (failed)
Result set
If OBJNAME is omitted, two result sets are returned.
Column Name Data Type Description
Database_name varchar (18) The name of the current database.
Database_size varchar (18) The size of the current database.
Unallocated Space Varchar (18) Unallocated space for the database.
Column Name Data Type Description
Reserved varchar (18) The total amount of space is retained.
Data VARCHAR (18) The total amount of space is used.
INDEX_SIZE VARCHAR (18) Index The space used.
Unused varchar (18) Unused space.
If you specify a parameter, return the following result set.
Column Name Data Type Description
Name NVARCHAR (20) The table name of the request space is requested.
Rows Char (11) ObjName table existing rows.
Reserved varchar (18) The total amount of space retained for the ObjName table.
Data varchar (18) The amount of space used in the ObjName table.
Index_size varchar (18) The amount of space used in the ObjName table.
Unuse varchar (18) Unused space in the ObjName table.
Comment
Sp_spaceused calculates the amount of disk space used by data and index and the amount of disk space used in the current database. If there is no ObjName, sp_spaceuses report to the space used throughout the current database.
When you specify UpdateUSAGE, Microsoft? SQL Server® scans the data pages in the database and makes any necessary corrections for the SysIndexes table on the storage space used by each table. For example, some cases occur: After removing the index, the SysIndexes information of the table may not be current. This process may take some time to run on a large table or database. This process should only be used when the value returned is incorrect, and the process is not negatively impacting other users or processes in the database. If the process is preferred, DBCC UpdateUsage can be run separately.
Authority
Perform permissions The default is granted public roles.
Example
A. Space information about the table
The following example reports the space amount of the Titles table allocates (reserves), the amount of space used, the amount of space used, and the amount of unused space reserved by the database object.
USE PUBS
EXEC SP_SPACEUSED 'TITLES'
B. Updated spatial information about the entire database
The following example summarizes the space used by the current database and uses an optional parameter @UpdateUSAGE. USE PUBS
sp_spaceused @updateusage = 'True'
