How to obtain the capacity size of a table in a SQL Server database

server| Data | database

In fact, as long as the use of the system built-in stored procedures sp_spaceused can get information about the table

such as: sp_spaceused ' tablename '

The following is a stored procedure for easy writing, which is to save all information about the current table in a specified table

CREATE PROCEDURE Get_tableinfo as

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 ' @tbnamevarchar (255) ',
@tbname = @tablename

FETCH NEXT from Info_cursor
Into @tablename
End

Close Info_cursor
Deallocate info_cursor
Go

Executing stored procedures
EXEC Get_tableinfo

The result of the query after running the stored procedure
SELECT *
From Tablespaceinfo
Order BY CAST (LTrim (RTrim (reserved)), Len (LTrim rtrim (reserved))