Finding SQL table size
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: