How to get the table size and row count of all the tables in the MDB Database?

Document ID : KB000050267
Last Modified Date : 14/02/2018
Show Technical Document Details

Login to SQL Server as Administrator/sa account -> Get into the MDB Database

Create a new Stored procedure with the below code

 CREATE PROCEDURE GetAllTableSizes       
 AS 
 DECLARE @TableName VARCHAR(100)
 DECLARE tableCursor CURSOR
 FOR
 select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1
 FOR READ ONLY
 CREATE TABLE #TempTable ( tableName varchar(100), numberofRows int, 
 reservedSize varchar(50), dataSize varchar(50), indexSize varchar(50), unusedSize varchar(50))
 OPEN tableCursor
 FETCH NEXT FROM tableCursor INTO @TableName
 WHILE (@@Fetch_Status >= 0)
 BEGIN
     INSERT #TempTable
         EXEC sp_spaceused @TableName
     FETCH NEXT FROM tableCursor INTO @TableName
 END
 CLOSE tableCursor
 DEALLOCATE tableCursor
 SELECT * FROM #TempTable order by numberofRows DESC
 DROP TABLE #TempTable
 GO

Then run the SQL Statement by pressing the F5 function key
EXEC GetAllTableSizes