Learn more about SQL Server tools

   
   















































Determining space used for each table in a SQL Server database

MSSQLTips author Greg Robidoux By:   |   Read Comments (14)   |   Related Tips: 1 | 2 | 3 | More > Monitoring

Problem
One thing that is often handy to know is how much space your tables are 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 the built-in reports in SQL Server 2005 or by using the Taskpad view in SQL Server 2000, but what is a simple way of retrieving this data without using the GUI or without having to write a query to gather this data from the system tables?

Solution
All the data that is retrieved for SQL Server Management Studio and SQL Server Enterprise Manager is pulled from queries that access the data in the system tables.  In a previous tip, How does Enterprise Manager get its data we took a look at how to use Profiler or Trace to find out what is occurring under the covers.  For this tip, basically the same thing was done where we capture the query that SQL Server is using and then modified it to meet our needs.

SQL Server 2000 or 2005

Here is a script that can be used for SQL Server 2000 or SQL Server 2005.  This is a modified version of what SQL Server Management Studio uses for SQL Server 2005.  The process creates a few temporary tables, determines a list of the tables, and then uses sp_spaceused to gather the stats into a temporary table and then outputs the results.

DECLARE @table_name VARCHAR(500
DECLARE @schema_name VARCHAR(500
DECLARE @tab1 TABLE(
        
tablename VARCHAR (500collate database_default
       
,schemaname VARCHAR(500collate database_default
)

CREATE TABLE #temp_Table (
        
tablename sysname
       
,row_count INT
       
,reserved VARCHAR(50collate database_default
       
,data VARCHAR(50collate database_default
       
,index_size VARCHAR(50collate database_default
       
,unused VARCHAR(50collate database_default 
)

INSERT INTO @tab1 
SELECT Table_NameTable_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 
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 id FROM sysobjects WHERE id OBJECT_ID(@table_name))
        
BEGIN
               INSERT INTO 
#temp_Table EXEC sp_spaceused @table_namefalse;
       
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

SQL Server 2005

Here is another version of this same process.  The overall process is the same, but it uses the new tables in SQL Server 2005.  It also uses the Try Catch processing which was discussed in this previous tip, SQL Server 2005 - Try Catch Exception Handling. Both of the examples will produce the same ouput.

BEGIN try 
DECLARE @table_name VARCHAR(500) ; 
DECLARE @schema_name VARCHAR(500) ; 
DECLARE @tab1 TABLE(
        
tablename VARCHAR (500collate database_default
,       schemaname VARCHAR(500collate database_default
); 
DECLARE  @temp_table TABLE (    
        
tablename sysname
,       row_count INT
,       reserved VARCHAR(50collate database_default
,       data VARCHAR(50collate database_default
,       index_size VARCHAR(50collate database_default
,       unused VARCHAR(50collate 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 
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_namefalse ;
        
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,tablename;
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
,       AS index_sizeAS unusedAS schemaname 
END catch

Here is some sample output after running this against the AdventureWorks database.

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: 2/12/2007


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Friday, May 23, 2014 - 7:38:44 PM - Dave Haskell Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 @command1=@str
 
 SELECT * FROM @SpaceUsed


Thursday, August 16, 2012 - 5:07:38 PM - PraveenYamani Read The Tip

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 @command1=@str


Thursday, August 16, 2012 - 4:55:42 PM - PraveenYamani Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

 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 Read The Tip

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 @command1=@cmd1


Thursday, April 02, 2009 - 11:00:37 AM - JeffSkvorc Read The Tip

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




 
Sponsor Information