Pin Me

SQL: Finding the Size of Tables

written by: theinkandpen (Robert Mullon)•edited by: Robin L.•updated: 10/27/2008

A brief article discussing how to find the size of tables in SQL. Sample code provided to make the process easier.

  • slide 1 of 2

    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.

  • slide 2 of 2

    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:

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