How to obtain the occupied capacity of tables in the SQL Server database

In fact, you only need to use the built-in storage process sp_spaceused to obtain table-related information.
For example, sp_spaceused 'tablename'
The following is a stored procedure for writing convenience. The purpose is to store all the information about the current table in a specified table.
Create procedure get_tableinfo

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


Execute the Stored Procedure
Exec get_tableinfo
Query the results after the stored procedure is run.
Select *
From tablespaceinfo
Order by cast (left (ltrim (rtrim (reserved), len (ltrim (rtrim (reserved)-2) as int) desc