To get the table capacity size in the SQL Server database

I. Defining stored Procedures

The goal is to keep all the relevant information for 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 ' @tbname varchar (255) ',
@tbname = @tablename

FETCH NEXT from Info_cursor
Into @tablename
END

CLOSE Info_cursor
Deallocate info_cursor
GO

Ii. execution of stored procedures
EXEC Get_tableinfo

Third, query table data

SELECT *
From Tablespaceinfo
Order BY cast (left (LTrim (RTrim (reserved)), Len (LTrim (RTrim (reserved)))-2 as int) desc

************************************************************************************

Simple analysis:

1. Single table
EXEC sp_spaceused N ' table name ';

2. Database
Use [Database to view]
sp_spaceused

3. All databases

exec sp_msforeachdb @Command1 = "Use?" EXEC sp_spaceused "

************************************************************************************

To get the table capacity size in the SQL Server database