Finding SQL table size
Page content

Introduction

In order to determine the size of table in an SQL database, you need to recall something named a ‘stored procedure’, or a program present within your server applications, which calculates things such as the different tables there are in the database, the size of the tables, what the biggest table is etc. This is very useful if you want to find out how much disk space a table is using for instance.

There are different ways of finding out the table size of your database, such as the Enterprise manager method or using the Query analyzer. However, it is much simpler to first write the stored procedure and then recall the script every single time you need to determine the above aspects.

Stored Procedure Script

Here is the script for the stored procedure which can then recall each time you need to find out the table sizes. This works on SQL server 2000 but isn’t tested on other versions, although it may work anyway.

CREATE PROCEDURE GetAllTableSizes

AS

/*

Obtains spaced used data for ALL user tables in the database

*/

DECLARE @TableName VARCHAR(100) –For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing

DECLARE tableCursor CURSOR

FOR

select [name]

from dbo.sysobjects

where OBJECTPROPERTY(id, N’IsUserTable’) = 1

FOR READ ONLY

--A procedure level temp table to store the results

CREATE TABLE #TempTable

(

tableName varchar(100),

numberofRows varchar(100),

reservedSize varchar(50),

dataSize varchar(50),

indexSize varchar(50),

unusedSize varchar(50)

)

--Open the cursor

OPEN tableCursor

--Get the first table name from the cursor

FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch

WHILE (@@Fetch_Status >= 0)

BEGIN

--Dump the results of the sp_spaceused query to the temp table

INSERT #TempTable

EXEC sp_spaceused @TableName

--Get the next table name

FETCH NEXT FROM tableCursor INTO @TableName

END

--Get rid of the cursor

CLOSE tableCursor

DEALLOCATE tableCursor

--Select all records so we can use the reults

SELECT *

FROM #TempTable

--Final cleanup!

DROP TABLE #TempTable

GO

You can then execute the following command in order to get table sizes:

EXEC GetAllTableSizes

This script is courtesy of Mitchell Sellers.com and can be downloaded here:

https://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx