SEARCH

How-To Geek

Determine Size of a Table in SQL Server

Ever wonder how big a table really is in your database? You know there are a million rows in the table, but how much space is that really taking?

SQL Server provides a built-in stored procedure that you can run to easily show the size of a table, including the size of the indexes… which might surprise you.

Syntax:

sp_spaceused ‘Tablename’

Here’s an example of it in action:

Highly useful stuff. It’s always interesting to see that the indexes are bigger than the actual data.

Lowell Heddings, better known online as the How-To Geek, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on if you'd like.

  • Published 03/27/07

Comments (20)

  1. sMiGuMp

    While this is a useful command for determining the space allocated to a table, keep in mind that sp_spaceused references the sysindexes system table to get the space attributes and sometimes what’s stored there can get be out of synch with reality. If you see space numbers that are seemingly out of whack (especially for the ‘unused’ value), run a “DBCC UPDATEUSAGE” command against the table in question before running sp_spacesued (or against the entire database if you have time and aren’t worried about stepping on anyone’s toes).

    This can automatically be done as an optional parameter within sp_spaceused…

    e.g. sp_spaceused ‘Orders’,true

    …or as separate commands as shown below.

    To update usage stats for the entire current database, the syntax is:
    DBCC UPDATEUSAGE (0)

    For a specific, named database:
    DBCC UPDATEUSAGE (‘InsertdDbNameHere’)

    For a specific table:
    DBCC UPDATEUSAGE (‘InsertdDbNameHere’,’InsertdTableNameHere’)

    You can always check BOL for all the details on DBCC UPDATEUSAGE

  2. Charles tucker

    Thank you, using query analyer and running procedure sp_spaceused is helpful, however, what if your database has close too 100 tables. Anyway to return the entire database size?

  3. Rick

    I just found this, it returns spaceused for each table:

    EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?'”

  4. Sanjiv

    Charles sp_helpdb

  5. Brian

    Rick – that is one of the most useful sql server commands of all time. You are the greatest.

  6. LNu76

    I tried to use Ricks Tipp, but having 181 Tables in the Database didnt do it much good. Also, his Mechanic with sp_spaceused treated each Table as a standalone query, wich made it impossible to use in an automated Growth-of-Database Statistic.

    I stole/borrowed the logic of sp_spaceused and applied it to the following VIEW (wich I now add to a Statistics Table, wich can then be queried in several ways):

    SELECT
    TableName,
    NumRows,
    reservedpages *8192/1024 as TotalSpace,
    pages * 8192/1024 as DataSpace,
    (usedpages-pages)*8192/1024 as IndexSpace,
    (reservedpages-usedpages)*8192/1024 as UnusedSpace

    FROM (SELECT
    t.[name] as tablename,
    avg([rows]) as NumRows,
    sum(total_pages) as reservedpages,
    sum(used_pages) as usedpages,
    sum(
    CASE
    When it.internal_type IN (202,204) Then 0
    When a.type 1 Then a.used_pages
    When p.index_id

  7. LNu76

    Bah, it got cut off, here is the rest:

    When p.index_id

  8. LNu76

    When p.index_id

  9. LNu76

    (smaller than sign – why doesnt it take it here?) 2 Then a.data_pages
    Else 0
    END) as pages
    from sys.allocation_units as a Join sys.partitions as p on p.partition_id = a.container_id
    left join sys.internal_tables it on p.object_id = it.object_id
    JOIN sys.tables as t on p.object_id=t.object_id
    –WHERE t.name=’mittra’
    group by t.[name]) as subselect

  10. Laura

    Rick, THANKS MATE!!!!! So helpful!

  11. Yuri

    Rick, thank you!

  12. Nausif

    im using TSQLFundamentals2008 database and this database contains Schema name HR with table employees in it.
    I tried the following code gives an error.

    use TSQLFundamentals2008
    exec sp_spaceused HR.Employees

    –Error
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘.’.

    I tried using the code without Schema name

    use TSQLFundamentals2008
    exec sp_spaceused Employees

    –Error
    Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62
    The object ‘Employees’ does not exist in database ‘TSQLFundamentals2008′ or is invalid for this operation.

    pls help

  13. NItin

    Hey Nausif,

    Try running

    sp_spaceused ‘HR.Employees’

    The above will run without errors

  14. stephen

    Am new to SQL. I want to have a list of all the tables and their sizes so that I see which one has grown out of hand. sp_spaceused means that I have to do that individually for all 70 tables. sp_helpdb shows just the full table

  15. supereds28ph

    hey guys try this codes.. just like what sp_spaceused do. you just have to run this script to your target database.

    DECLARE @table_name VARCHAR(500)
    DECLARE @schema_name VARCHAR(500)
    DECLARE @tab1 TABLE(
    tablename VARCHAR (500) collate database_default
    ,schemaname VARCHAR(500) collate database_default
    )
    CREATE TABLE #temp_Table (
    tablename sysname
    ,row_count BIGINT
    ,reserved VARCHAR(50) collate database_default
    ,data VARCHAR(50) collate database_default
    ,index_size VARCHAR(50) collate database_default
    ,unused VARCHAR(50) collate database_default
    )
    INSERT INTO @tab1
    SELECT Table_Name, Table_Schema
    FROM information_schema.tables
    WHERE TABLE_TYPE = ‘BASE TABLE’
    DECLARE c1 CURSOR FOR
    SELECT Table_Schema + ‘.’ + Table_Name
    FROM information_schema.tables t1
    WHERE TABLE_TYPE = ‘BASE TABLE’
    OPEN c1
    FETCH NEXT FROM c1 INTO @table_name
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @table_name = REPLACE(@table_name, ‘[‘,”);
    SET @table_name = REPLACE(@table_name, ‘]’,”);
    — make sure the object exists before calling sp_spaceused
    IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name))
    BEGIN
    INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false;
    END
    FETCH NEXT FROM c1 INTO @table_name
    END
    CLOSE c1
    DEALLOCATE c1
    SELECT t1.*
    ,t2.schemaname
    FROM #temp_Table t1
    INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
    ORDER BY schemaname,t1.tablename;
    DROP TABLE #temp_Table

  16. Nausif

    thanx NItin it worked…

  17. kelvin

    awesome thread and advice-thanks.

  18. rk

    Here is the one with Schema names as well
    =====================================

    SELECT
    schemaname,
    TableName,
    NumRows,
    reservedpages *8192/1024 as TotalSpace,
    pages * 8192/1024 as DataSpace,
    (usedpages-pages)*8192/1024 as IndexSpace,
    (reservedpages-usedpages)*8192/1024 as UnusedSpace
    FROM (SELECT
    s.[name] as schemaname,
    t.[name] as tablename,
    avg([rows]) as NumRows,
    sum(total_pages) as reservedpages,
    sum(used_pages) as usedpages,
    sum(
    CASE
    When it.internal_type IN (202,204) Then 0
    When a.type = 1 Then a.used_pages
    When p.index_id < 2 Then a.data_pages
    Else 0
    END) as pages
    from sys.allocation_units as a Join sys.partitions as p on p.partition_id = a.container_id
    left join sys.internal_tables it on p.object_id = it.object_id
    JOIN sys.tables as t on p.object_id=t.object_id
    join sys.schemas as s on t.schema_id = s.schema_id
    –WHERE t.name= 'Constraints.Level60Zone'
    group by s.[name], t.[name]) as subselect

    order by schemaname , tablename

  19. PC

    You can keep the main system sp_spaceused with the following script to get the full DB space usage
    ————————————————————————————————————————————–

    CREATE TABLE #TmpTableUsage (TableName varchar(100), NumRows int, TotalSpace int,DataSpace int, IndexSpace int, UnusedSpace int)

    If isnull(Object_id(‘Tempdb..#TmpSpaceUsage’),0) 0
    DROP TABLE #TmpSpaceUsage

    CREATE TABLE #TmpSpaceUsage (TableName varchar(100), NumRows int, TotalSpace varchar(50),DataSpace varchar(50), IndexSpace varchar(50), UnusedSpace varchar(50))

    Insert into #TmpSpaceUsage
    EXEC sp_MSforeachtable @command1= “EXEC sp_spaceused ‘?'”

    Insert into #TmpTableUsage
    select TableName,
    NumRows,
    cast(substring(TotalSpace,1,Len(TotalSpace)-3) as Int) as TotalSpace,
    cast(substring(DataSpace,1,Len(DataSpace)-3) as Int) as DataSpace,
    cast(substring(IndexSpace,1,Len(IndexSpace)-3) as Int) as IndexSpace,
    cast(substring(UnusedSpace,1,Len(UnusedSpace)-3) as Int) as UnusedSpace
    from #TmpSpaceUsage

    Select * from #TmpTableUsage

  20. PC

    Not valid solution from Lnu76 or RK when you have multiple partition on a table doesn’t add up also indexspace is always at zero which is wrong also.

    I reformatted to get by top tables including average space usage by row

    If isnull(Object_id(‘Tempdb..#TmpTableUsage’),0) 0
    DROP TABLE #TmpTableUsage

    CREATE TABLE #TmpTableUsage (TableName varchar(100), NumRows int, TotalSpace int,DataSpace int, IndexSpace int, UnusedSpace int)

    If isnull(Object_id(‘Tempdb..#TmpSpaceUsage’),0) 0
    DROP TABLE #TmpSpaceUsage

    CREATE TABLE #TmpSpaceUsage (TableName varchar(100), NumRows int, TotalSpace varchar(50),DataSpace varchar(50), IndexSpace varchar(50), UnusedSpace varchar(50))

    Insert into #TmpSpaceUsage
    EXEC sp_MSforeachtable @command1= “EXEC sp_spaceused ‘?'”

    Insert into #TmpTableUsage
    select TableName,
    NumRows,
    cast(substring(TotalSpace,1,Len(TotalSpace)-3) as Int) as TotalSpace,
    cast(substring(DataSpace,1,Len(DataSpace)-3) as Int) as DataSpace,
    cast(substring(IndexSpace,1,Len(IndexSpace)-3) as Int) as IndexSpace,
    cast(substring(UnusedSpace,1,Len(UnusedSpace)-3) as Int) as UnusedSpace
    from #TmpSpaceUsage

    Select
    TableName,
    NumRows,
    AvgSpacePerRow = CASE When CONVERT(DECIMAL(10,2),TotalSpace) > 0 Then CONVERT(DECIMAL(10,2), CONVERT(DECIMAL(10,2),TotalSpace)/NumRows) Else 0 End,
    CONVERT(DECIMAL(10,2),CONVERT(DECIMAL(10,2),TotalSpace) / 1024) as TotalSpaceMB,
    CONVERT(DECIMAL(10,2),CONVERT(DECIMAL(10,2),DataSpace) / 1024) as DataSpaceMB,
    CONVERT(DECIMAL(10,2),CONVERT(DECIMAL(10,2),IndexSpace) / 1024) as IndexSpaceMB,
    UnusedSpace as UnusedSpaceKB
    from #TmpTableUsage
    Where NumRows >=1
    order by TotalSpace Desc

Enter Your Email Here to Get Access for Free:

Go check your email!