Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

Determining space used for all tables in a SQL Server database


By:   |   Read Comments (15)   |   Related Tips: 1 | 2 | 3 | More > Monitoring

Problem

One thing that is often handy is to know how much space each table is using within your database.  It is helpful to know the number of rows, the data space used as well as the index space used. There are several ways that you can get this information, by reading the system tables, using SSMS and using the built-in reports in SSMS.  In this tip we look at some queries that can be used to do this.

Solution

This tip was originally written in 2007 and it used the queries that SSMS used to get the details.  To make this a little easier to use, the tip was updated with the following approach. This has been tested with SQL Server 2017 and should work with all previous versions, since sp_spaceused has been around for quite some time.

There are several ways to pull the data to find out the space used for a table.  One simple way to do this is to use sp_spaceused to get the space used for a table. We will use the AdventureWorks database for this test.

Here is the simple command to get the data for one table.

sp_spaceused '[HumanResources].[Department]'

This returns the following information:

sp_spaceused output

This is great if you want to do one table at a time, but what if you want to do all of the tables.  You can use this code as suggested in the comments section:

DECLARE @str VARCHAR(500)
SET @str = 'exec sp_spaceused ''?'''
EXEC sp_msforeachtable @[email protected]

This is helpful, but the output is not very easy to read.

sp_spaceused several tables

So what if we put the results to a temp table as shown in the comments section and then we could sort the data as needed like this:

CREATE TABLE #SpaceUsed (
	 TableName sysname
	,NumRows BIGINT
	,ReservedSpace VARCHAR(50)
	,DataSpace VARCHAR(50)
	,IndexSize VARCHAR(50)
	,UnusedSpace VARCHAR(50)
	) 

DECLARE @str VARCHAR(500)
SET @str =  'exec sp_spaceused ''?'''
INSERT INTO #SpaceUsed 
EXEC sp_msforeachtable @[email protected]

SELECT * FROM #SpaceUsed ORDER BY TableName

When we run this we get the following output sorted by table name:

sp_spaceused all tables

This is great, but if we try to sort by ReservedSpace as follows.

SELECT * FROM #SpaceUsed ORDER BY ReservedSpace desc

The sorting doesn't work correctly.  It thinks ResevedSpace is a text column, so it sorts in text order, not numerical order.

sp_spaceused sorted data

So what we can do is convert all of the space columns to numbers, by removing the KB and also converting the values to MB instead of KB as follows.

SELECT TableName, NumRows, 
CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024 as ReservedSpace_MB,
CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024 as DataSpace_MB,
CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024 as IndexSpace_MB,
CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024 as UnusedSpace_MB
FROM #SpaceUsed
ORDER BY ReservedSpace_MB desc

Now the sorting will work correctly as follows:

sp_spaceused sort correctly

Entire script to get sortable space used information for all SQL Server tables in database

Here is the complete script.  You can change the ORDER BY as needed.

IF OBJECT_ID('tempdb..#SpaceUsed') IS NOT NULL
	DROP TABLE #SpaceUsed

CREATE TABLE #SpaceUsed (
	 TableName sysname
	,NumRows BIGINT
	,ReservedSpace VARCHAR(50)
	,DataSpace VARCHAR(50)
	,IndexSize VARCHAR(50)
	,UnusedSpace VARCHAR(50)
	) 

DECLARE @str VARCHAR(500)
SET @str =  'exec sp_spaceused ''?'''
INSERT INTO #SpaceUsed 
EXEC sp_msforeachtable @[email protected]

SELECT TableName, NumRows, 
CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024 as ReservedSpace_MB,
CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024 as DataSpace_MB,
CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024 as IndexSpace_MB,
CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024 as UnusedSpace_MB
FROM #SpaceUsed
ORDER BY ReservedSpace_MB desc
Downside to using sp_spaceused

One of the major downsides to using sp_spaceused is that it only returns the table name and does not include the schema name.  So, if you have several tables with the same name, but with different schemas it is kind of hard to tell which table it is.

Next Steps
  • Here is another simple process to help you get a handle on your database and table usage
  • Add these scripts to your toolbox
  • Run these scripts on a set schedule like once a week or once a month to get a handle on how your database and tables are changing.  This can then be used for trending and space requirements as you do future planning for your database.


Last Update:

First Published: 2007-02-12


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, October 19, 2015 - 11:44:41 AM - Rob Kraft Back To Top

I like to use this script, but I always make a small change to sort by the data size.  Specifically changing the final ORDER BY to

ORDER BYlen(data)desc, data desc, schemaname,t1.tablename;

 


Friday, May 23, 2014 - 7:38:44 PM - Dave Haskell Back To Top

Thank you!  But can you explain why my table sizes would add up to 5.14 gigs (data used, not reserved) but the .BAK file from my database would only take up 1.55 gigs of disk space?  Are SQL Server database backup files compressed?


Monday, April 07, 2014 - 7:48:48 AM - Greg Robidoux Back To Top

Hi Peter, yes you can just use sp_spaceused along with the table name to just see how much space one table is using.


Sunday, April 06, 2014 - 6:27:11 AM - Peter Back To Top

Hi,

 

Will it be possible to check the table size for certain tables only, rather than all tables?

 

Thanks.

 

 


Monday, November 05, 2012 - 2:10:38 AM - Rendy Back To Top

Thanks very much for the script!I'm a dummy in SQL and the script is very useful!

Now i can find out which table is using the most space with ease!


Thursday, August 16, 2012 - 5:08:59 PM - PraveenYamani Back To Top

Oops! Use this..


 DECLARE @SpaceUsed TABLE( TableName VARCHAR(100)
      ,No_Of_Rows BIGINT
      ,ReservedSpace VARCHAR(15)
      ,DataSpace VARCHAR(15)
      ,Index_Size VARCHAR(15)
      ,UnUsed_Space VARCHAR(15)
      ) 
DECLARE @str VARCHAR(500)
SET @str =  'exec sp_spaceused ''?'''
INSERT INTO @SpaceUsed EXEC sp_msforeachtable @[email protected]
 
 SELECT * FROM @SpaceUsed


Thursday, August 16, 2012 - 5:07:38 PM - PraveenYamani Back To Top

OR Simply use this..

DECLARE @SpaceUsed TABLE( TableName VARCHAR(100)
      ,No_Of_Rows BIGINT
      ,ReservedSpace VARCHAR(15)
      ,DataSpace VARCHAR(15)
      ,Index_Size VARCHAR(15)
      ,UnUsed_Space VARCHAR(15)
      ) 
DECLARE @str VARCHAR(500)
SET @str =  'exec sp_spaceused ''?'''
INSERT INTO @SpaceUsed EXEC sp_msforeachtable @[email protected]


Thursday, August 16, 2012 - 4:55:42 PM - PraveenYamani Back To Top

We can get this result like this too.


DECLARE @tables TABLE(TabID INT IDENTITY(1,1)
      ,TableName VARCHAR(100)
      ,SchamaName VARCHAR(20)
      )

DECLARE @SpaceUsed TABLE( TableName VARCHAR(100)
      ,No_Of_Rows BIGINT
      ,ReservedSpace VARCHAR(15)
      ,DataSpace VARCHAR(15)
      ,Index_Size VARCHAR(15)
      ,UnUsed_Space VARCHAR(15)
      )     

DECLARE @TabCount INT
DECLARE @TableName VARCHAR(100)
DECLARE @Start INT =1
INSERT INTO @tables
 SELECT st.name,ss.name FROM sys.tables st join sys.schemas ss
   ON(st.schema_id = ss.schema_id)
SET @TabCount = @@ROWCOUNT


 WHILE @Start < @TabCount
 BEGIN
 SELECT @TableName = TableName FROM @tables
  WHERE TabID = @Start
INSERT INTO @SpaceUsed
  EXEC sp_spaceused @TableName
 SET @Start = @Start + 1
 END 
 
 
 SELECT su.*,t.SchamaName FROM  @SpaceUsed su JOIN @tables t
    ON su.TableName = t.TableName
 
 
 


Wednesday, June 13, 2012 - 6:05:04 AM - steve Back To Top

set nocount on

declare  @objname sysname
   ,@objtype sysname
declare  @spaceused table (objtype sysname null, name sysname primary key, rows int, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100))

select  top 1 @objname = '[' + s.name + '].[' + o.name + ']'
   ,@objtype = o.type_desc
from  sys.objects o
join  sys.schemas s on o.schema_id = s.schema_id
order by 1

while  @@ROWCOUNT > 0
begin

 if @objtype not in ('SQL_STORED_PROCEDURE','SQL_INLINE_TABLE_VALUED_FUNCTION','PRIMARY_KEY_CONSTRAINT','SQL_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION','UNIQUE_CONSTRAINT')
 begin
  insert @spaceused (name, rows, reserved, data, index_size, unused)
  exec sp_spaceused @objname
  update @spaceused set objtype = @objtype, name = @objname where objtype is null
 end else begin
  insert @spaceused (name, objtype)
  select @objname, @objtype
 end
  
 select  top 1 @objname = '[' + s.name + '].[' + o.name + ']'
    ,@objtype = o.type_desc
 from  sys.objects o
 join  sys.schemas s on o.schema_id = s.schema_id
 where  '[' + s.name + '].[' + o.name + ']' > @objname
 order by 1

end

update @spaceused
set  reserved = REPLACE(reserved, ' KB', '')
  ,data  = REPLACE(data, ' KB', '')
  ,index_size = REPLACE(index_size, ' KB', '')
  ,unused  = REPLACE(unused, ' KB', '')

select *
from @spaceused


Monday, March 26, 2012 - 5:59:55 AM - Nazer Mohamed Back To Top

http://nazermohamed.blogspot.co.uk/2012/03/quick-dump-of-all-table-sizes-of.html

 


Monday, January 30, 2012 - 1:39:24 PM - Vojtech Machacek Back To Top

Well, I tried my best and this is what I came up with:

 

USE SECREADER

BEGIN try
DECLARE @table_name VARCHAR(500) ;
DECLARE @schema_name VARCHAR(500) ;
DECLARE  @temp_table TABLE (   
        tablename sysname
,       row_count INT
,       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
,        row_id INT IDENTITY(1,1)
);

DECLARE c1 CURSOR FOR
SELECT t2.name + '.' + t1.name 
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );  

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_spacedused
        IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
        BEGIN               
                INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
                UPDATE @temp_table SET tablename = @table_name WHERE row_id =
                    (SELECT MAX(row_id) FROM @temp_table)
        END
       
        FETCH NEXT FROM c1 INTO @table_name;
END;
CLOSE c1;
DEALLOCATE c1;

INSERT INTO [DbStatistics] ( DateReporting, TableName, [RowCount], Reserved, Data, IndexSize, Unused )
SELECT GETDATE() as DateReporting, tablename as TableName, row_count as [RowCount], CONVERT(INT,REPLACE(reserved,'KB','')) as Reserved, CONVERT(INT,REPLACE(data,'KB','')) as Data, CONVERT(INT,REPLACE(index_size,'KB','')) as IndexSize, CONVERT(INT,REPLACE(unused,'KB','')) as Unused
FROM @temp_table;

SELECT 1

 

END try
BEGIN catch
SELECT -100 AS l1
,       ERROR_NUMBER() AS tablename
,       ERROR_SEVERITY() AS row_count
,       ERROR_STATE() AS reserved
,       ERROR_MESSAGE() AS data
,       1 AS index_size, 1 AS unused, 1 AS schemaname
END catchUSE SECREADER

BEGIN try
DECLARE @table_name VARCHAR(500) ;
DECLARE @schema_name VARCHAR(500) ;
DECLARE  @temp_table TABLE (   
        tablename sysname
,       row_count INT
,       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
,        row_id INT IDENTITY(1,1)
);

DECLARE c1 CURSOR FOR
SELECT t2.name + '.' + t1.name 
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );  

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_spacedused
        IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
        BEGIN               
                INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
                UPDATE @temp_table SET tablename = @table_name WHERE row_id =
                    (SELECT MAX(row_id) FROM @temp_table)
        END
       
        FETCH NEXT FROM c1 INTO @table_name;
END;
CLOSE c1;
DEALLOCATE c1;

INSERT INTO [DbStatistics] ( DateReporting, TableName, [RowCount], Reserved, Data, IndexSize, Unused )
SELECT GETDATE() as DateReporting, tablename as TableName, row_count as [RowCount], CONVERT(INT,REPLACE(reserved,'KB','')) as Reserved, CONVERT(INT,REPLACE(data,'KB','')) as Data, CONVERT(INT,REPLACE(index_size,'KB','')) as IndexSize, CONVERT(INT,REPLACE(unused,'KB','')) as Unused
FROM @temp_table;

SELECT 1

 

END try
BEGIN catch
SELECT -100 AS l1
,       ERROR_NUMBER() AS tablename
,       ERROR_SEVERITY() AS row_count
,       ERROR_STATE() AS reserved
,       ERROR_MESSAGE() AS data
,       1 AS index_size, 1 AS unused, 1 AS schemaname
END catch

 

 

 

It work with table

 

 

CREATE TABLE [dbo].[DbStatistics](
    [Id] [tinyint] IDENTITY(1,1) NOT NULL,
    [DateReporting] [datetime] NOT NULL,
    [TableName] [varchar](50) NOT NULL,
    [RowCount] [int] NOT NULL,
    [Reserved] [int] NOT NULL,
    [Data] [int] NOT NULL,
    [IndexSize] [int] NOT NULL,
    [Unused] [int] NOT NULL,
 CONSTRAINT [PK_DbStatistics] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 

Maybe somebody will find it usefull, Maybe Im missing something by saving those data...


Monday, January 30, 2012 - 1:06:58 PM - Vojtech Machacek Back To Top

This is briliant I have to say. Im just wondering if I can create procedure with will save those data into regular table with date of execution - like snapshot in time and save file sizes as numbers. Then I can make charts how DB was progressing and very nice other stuff. Can anybody help we with this task, Im not really database guy :(

 


Monday, June 21, 2010 - 1:35:28 PM - dpaproskijr Back To Top

 Here's another version. The original didn't work properly with repeated table names (i.e. schema1.table, schema2.table would result in more than 2 rows of output). This one also sorts by descending size.

BEGIN try
DECLARE @table_name VARCHAR(500) ;
DECLARE @schema_name VARCHAR(500) ;
DECLARE  @temp_table TABLE (    
        tablename sysname
,       row_count INT
,       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
,        row_id INT IDENTITY(1,1)
);

DECLARE c1 CURSOR FOR
SELECT t2.name + '.' + t1.name  
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   

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_spacedused
        IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
        BEGIN                
                INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
                UPDATE @temp_table SET tablename = @table_name WHERE row_id =
                    (SELECT MAX(row_id) FROM @temp_table)
        END
        
        FETCH NEXT FROM c1 INTO @table_name;
END;
CLOSE c1;
DEALLOCATE c1;

SELECT tablename, row_count, reserved, data, index_size, unused
    FROM @temp_table ORDER BY CONVERT(INT,REPLACE(data,'KB','')) DESC;

END try
BEGIN catch
SELECT -100 AS l1
,       ERROR_NUMBER() AS tablename
,       ERROR_SEVERITY() AS row_count
,       ERROR_STATE() AS reserved
,       ERROR_MESSAGE() AS data
,       1 AS index_size, 1 AS unused, 1 AS schemaname
END catch


Wednesday, March 10, 2010 - 8:57:42 AM - smarch Back To Top

Or how about ... ? 

 

-- Show the size of all the tables in a database
declare @cmd1 varchar(500)

set @cmd1 =
 'exec sp_spaceused ''?'''

exec sp_msforeachtable @[email protected]


Thursday, April 02, 2009 - 11:00:37 AM - JeffSkvorc Back To Top

Hey, great tip!  It helped a lot.  I made a minor mod below which simply rips out the ' KB' and casts the data as INT.  Then, I sort the list by Reserved DESC since I am mostly trying to find which tables are taking up the most space.  I also wrapped into a SPROC for easy access.  Here is the changes below.  Again, thank you.  Saved me some time...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE ListTableSizes
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 BEGIN try 
  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
  ); 
  DECLARE  @temp_table TABLE (    
   tablename sysname
   , row_count INT
   , 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
    t1.name
    , t2.name 
   FROM sys.tables t1 
   INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   

  DECLARE c1 CURSOR FOR 
   SELECT t2.name + '.' + t1.name  
   FROM sys.tables t1 
   INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   

  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_spacedused
   IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_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.tablename AS TableName
   , t1.row_count AS TableRows
   , CAST(REPLACE(t1.reserved, ' KB', '') AS INT) AS ReservedSpace
   , CAST(REPLACE(t1.data, ' KB', '') AS INT) AS DataSize
   , CAST(REPLACE(t1.index_size, ' KB', '') AS INT) AS IndexSize
   , CAST(REPLACE(t1.unused, ' KB', '') AS INT) AS UnusedSpace
   , t2.schemaname AS SchemaName
  FROM @temp_table t1 
  INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
  ORDER BY CAST(REPLACE(t1.reserved, ' KB', '') AS INT) desc;

 END try 
 BEGIN catch 
  SELECT -100 AS l1
  , ERROR_NUMBER() AS tablename
  , ERROR_SEVERITY() AS row_count
  , ERROR_STATE() AS reserved
  , ERROR_MESSAGE() AS data
  , 1 AS index_size, 1 AS unused, 1 AS schemaname 
 END catch
END
GO


Learn more about SQL Server tools