Querying SQL Server table size and number

-- =======================================================================================================
--Description: Updates the size of the tables in the query database and stores the results in the data table
-- =======================================================================================================
IF EXISTS (SELECT * from sysobjects WHERE id = object_id (' [dbo].[ Sp_updatetablespaceinfo] ') and xtype = ' P ')
BEGIN
DROP PROCEDURE [dbo]. [Sp_updatetablespaceinfo]
END
Go
CREATE PROCEDURE [dbo]. [Sp_updatetablespaceinfo]
As
BEGIN
--Query whether there is a result store table
IF not EXISTS (SELECT * from sysobjects where id = object_id (n ' temp_tablespaceinfo ') and OBJECTPROPERTY (ID, n ' isusertable ' ) = 1)
BEGIN
--does not exist then creates
CREATE TABLE Temp_tablespaceinfo
(Name NVARCHAR (128),
Rows char (11),
Reserved VARCHAR (18),
Data VARCHAR (18),
Index_size VARCHAR (18),
Unused VARCHAR (18))
END
--Clear the data sheet
DELETE from Temp_tablespaceinfo

--Define temporary variables in the time-elapsed storage table name
DECLARE @tablename VARCHAR (255)

--use Cursors to read all table table names in the database
DECLARE table_list_cursor Cursor FOR
SELECT name from sysobjects
WHERE objectproperty (ID, N ' istable ') = 1 and name not as N ' #%% ' ORDER by name

--Open cursor
OPEN Table_list_cursor
--Read the first piece of data
FETCH NEXT from Table_list_cursor to @tablename

--Iterate over the queried table name
While @ @FETCH_STATUS = 0
BEGIN
--Check whether the current table is a user table
IF EXISTS (SELECT * from sysobjects WHERE id = object_id (@tablename) and OBJECTPROPERTY (ID, N ' isusertable ') = 1)
BEGIN
--the current table reads its information into the table
EXECUTE sp_executesql n ' INSERT into temp_tablespaceinfo EXEC sp_spaceused @tbname ', N ' @tbname varchar (255) ', @tbname = @ta Blename
END
--Read the next piece of data
FETCH NEXT from Table_list_cursor to @tablename
END

--Releasing cursors
CLOSE Table_list_cursor
Deallocate table_list_cursor
END

GO

---specific invocation method
EXEC Sp_updatetablespaceinfo

Querying SQL Server table size and number