Determining space used for all tables in a SQL Server database

By:   |   Comments (25)   |   Related: 1 | 2 | 3 | > 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. The latest test was done using SQL Server 2019 and this 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 @command1=@str

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

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

Script to get sortable space used info for all SQL Server tables in a 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 @command1=@str

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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, May 8, 2023 - 10:18:23 AM - Greg Robidoux Back To Top (91169)
Thanks Steve.

Friday, May 5, 2023 - 10:24:06 AM - Steve Gouin Back To Top (91157)
There is another way (probably faster) to extract this information directly from system tables instead of calling repeatedly the sp_spaceused procedure.
I extracted the query from the "Disk usage by table" standard report.


SELECT
a3.name AS [schemaname],
a2.name AS [tablename],
a1.rows as row_count,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.data * 8 AS data,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY 4 desc

Monday, February 6, 2023 - 9:16:39 AM - Greg Robidoux Back To Top (90883)
Hi Sana,

you would need to build a table to store historical data and set up a scheduled job to periodically capture the data. Then you could do comparisons of different points in time.

Friday, February 3, 2023 - 3:01:18 PM - sana Back To Top (90877)
This is great, Is there any way we get the historical changes in this specific table. Like How many rows added 1 year back, and how much space used that time, and How many rows were added a month ago like that

Friday, August 5, 2022 - 2:51:38 AM - Jan Potgieter Back To Top (90346)
Thanks Greg, just what I was looking for this morning!

Friday, May 13, 2022 - 6:15:32 AM - Yisus Back To Top (90085)
Hello, I found a query

EXEC sp_MSforeachdb 'USE ?
EXEC sp_spaceused @oneresultset = 1;

Tuesday, June 1, 2021 - 10:06:36 AM - Greg Robidoux Back To Top (88779)
Hi S,

the only way to figure that out would be to run this process each day to collect the data and then put together some queries to do a differential between dates.

Tuesday, June 1, 2021 - 4:46:48 AM - s Back To Top (88774)
How can we get to know the space used for one particular date?

Thursday, July 30, 2020 - 5:20:05 AM - Steve Johnson Back To Top (86216)

I always right-click the DB in SSMS Object Explorer-->Reports-->Standard Reports-->Disk Usage By Table. Returns row counts, Reserved space, Used space (data vs indexes) and unused space.


Monday, September 23, 2019 - 4:28:08 PM - Kevin Clarke Back To Top (82548)

It worked like a charm - thanks, man!

FYI, I enhanced it (with a specific database option and by combining data and index space usage into 1 field) and then submitted it to Quora with a backlink to this page.

Just my way of saying thanks.


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

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 (30914)

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 7, 2014 - 7:48:48 AM - Greg Robidoux Back To Top (29986)

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 6, 2014 - 6:27:11 AM - Peter Back To Top (29981)

Hi,

 

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

 

Thanks.

 

 


Monday, November 5, 2012 - 2:10:38 AM - Rendy Back To Top (20213)

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 (19080)

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 Back To Top (19079)

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 Back To Top (19078)

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 (17960)

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 (16607)

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 (15839)

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 (15838)

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 (5725)

 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 (5035)

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 2, 2009 - 11:00:37 AM - JeffSkvorc Back To Top (3119)

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















get free sql tips
agree to terms