How to obtain the stored procedure of 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