View the amount of space each table occupies in the MSSQL database

To view the size of the database table, query SQL Server online to get the following statement from the book:

sp_spaceused

Displays the number of rows, 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']

Parameters

[@objname =] ' objname '

is the name of the table for which the Space usage information (reserved and allocated space) is requested. The objname data type is nvarchar (776), which is set to NULL by default.

[@updateusage =] ' updateusage '

Indicates that DBCC UPDATEUSAGE should be run within the database (when objname is not specified) or on a specific object (when objname is specified). The value can be true or false. The data type of the updateusage is varchar (5), which is set to FALSE by default.

Return code value

0 (Success) or 1 (failed)

Example A. Spatial information about a table

The following example reports the amount of space allocated (reserved) for a titles 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 ‘titles‘

B. Updated spatial information about the entire database

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

USE pubssp_spaceused @updateusage = ‘TRUE‘

However, this method can only see the size of a table, a database typically has more than one table, how to view a database of all the table size?

The first method (simpler, somewhat difficult to see):

exec sp_msforeachtable "exec sp_spaceused '?"

The second method (more complex, but see the more clearly, 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--Creating a result store table
(Nameinfo varchar (50),
rowsinfo int, reserved varchar (20),
Datainfo varchar (20),
Index_size varchar (20),
Unused varchar (20))

Delete from Tablespaceinfo--empty data table

DECLARE @tablename varchar (255)--table name

DECLARE @cmdsql varchar (500)

DECLARE info_cursor Cursor FOR
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: Display database information
sp_spaceused @updateusage = ' TRUE '

--itlearner Note: Display table information
SELECT *
From Tablespaceinfo
Order BY cast (left (LTrim (RTrim (reserved)), Len (LTrim (RTrim (reserved)))-2 as int) desc

The third method:

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

View the amount of space each table occupies in the MSSQL database