View the space occupied by each table in the MSSQL database

ArticleDirectory

• Sp_spaceused

Sp_spaceused

Displays the number of rows, the reserved disk space, and the disk space used by the tables in the current database, or the disk space reserved and used by the entire database.

Syntax

Sp_spaceused[[@ Objname =]'Objname']
[,[@ Updateusage =]'Updateusage']

Parameters

[@ Objname =]'Objname'

Is the table name used for the requested space (reserved and allocated space.ObjnameThe data type of isNvarchar (776)The default value is null.

[@ Updateusage =]'Updateusage'

Indicates that it should be in the database (not specifiedObjnameIs still on a specific object (specifyObjnameRun DBCC updateusage. The value can beTrueOrFalse.UpdateusageThe data type of isVarchar (5)The default value is false.

Return Code Value

0 (successful) or 1 (failed)

Example A. Table space information

The following example showsTitlesThe amount of space allocated (retained) by the table, the amount of space used by the data, the amount of space used by the index, and the amount of unused space reserved by the database object.

 
Use pubsexec sp_spaceused 'tidles'
 

B. Updated space information about the entire database

The following example summarizes the space used by the current database and uses optional parameters.@ Updateusage.

 
Use pubssp_spaceused @ updateusage = 'true'
 

However, this method only allows you to view the size of one table. A database usually has multiple tables. How can you view the size of all tables in a database at a time?

The first method (relatively simple, difficult to see ):

Exec sp_msforeachtable "Exec sp_spaceused '? '"

Method 2 (complex, but clear, the original author is unknown ):

If not exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [tablespaceinfo] ') and objectproperty (ID, n' isusertable') = 1)
Create Table tablespaceinfo -- create a result storage table
(Nameinfo varchar (50 ),
Rowsinfo int, reserved varchar (20 ),
Datainfo varchar (20 ),
Index_size varchar (20 ),
Unused varchar (20 ))

Delete from tablespaceinfo -- clear the data table

Declare @ tablename varchar (255) -- table name

Declare @ brief SQL varchar (500)

Declare info_cursor cursor
Select O. Name
From DBO. sysobjects O where objectproperty (O. ID, N 'istable') = 1
And O. Name Not Like n' # % 'order by O. Name

Open info_cursor

Fetch next from info_cursor
Into @ tablename

While @ fetch_status = 0
Begin

If exists (select * From DBO. sysobjects where id = object_id (@ tablename) and objectproperty (ID, N 'isusertable') = 1)
Execute sp_executesql
N 'insert into tablespaceinfo exec sp_spaceused @ tbname ',
N' @ tbname varchar (255 )',
@ Tbname = @ tablename

Fetch next from info_cursor
Into @ tablename
End

Close info_cursor
Deallocate info_cursor
Go

-- Itlearner Note: displays database information
Sp_spaceused @ updateusage = 'true'

-- Itlearner Note: displays table information
Select *
From tablespaceinfo
Order by cast (left (ltrim (rtrim (Reserved), Len (ltrim (rtrim (Reserved)-2) As INT) DESC

Method 3:

Select object_name (ID) tablename, 8 * Reserved/1024 reserved, rtrim (8 * dpages/1024) + 'mb' used, 8*(Reserved-dpages)/1024 unused, 8 * dpages/1024-rows/1024 * minlen/1024 free,
Rows, * From sysindexes
Where indid = 1
Order by reserved DESC