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

book

Article ID: 50267

calendar_today

Updated On:

Products

CA IT Asset Manager CA Software Asset Manager (CA SAM) ASSET PORTFOLIO MGMT- SERVER SUPPORT AUTOMATION- SERVER CA Service Desk Manager - Unified Self Service KNOWLEDGE TOOLS CA Service Management - Asset Portfolio Management CA Service Management - Service Desk Manager

Issue/Introduction

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

Environment

Release:
Component: ARGIS