The following is a quick method to find the disk space used by each table in a database. It uses the sp_msforeachtable stored procedure to iterate through all the tables in the database.
Enter command:
sp_msforeachtable 'sp_spaceused "?"'
The ? serves as a placeholder for each table name.
Reader Feedback
Dave W. writes: This tip works fairly well. However, it's got two problems: 1) If you have a table with '?' in its name, it will crash (I know: using ? in a table name is bad, mmmkay?); and 2) It produces somewhat unwieldy output. My solution was to modify Microsoft's sp_spaceused and create a new one called sp_spaceused_all, shown below. Here is its more compact output:
name rows reserved data index_size unused
------------------------------ ----------- ------------------ ------------------ ------------------ ------------------
Orders 830 504 KB 160 KB 320 KB 24 KB
Order Details 2155 328 KB 72 KB 208 KB 48 KB
Employees 9 320 KB 232 KB 48 KB 40 KB
Categories 8 176 KB 112 KB 32 KB 32 KB
syscolumns 455 168 KB 64 KB 56 KB 48 KB
sysindexes 79 128 KB 56 KB 16 KB 56 KB
Customers 91 104 KB 24 KB 80 KB 0 KB
Products 77 104 KB 8 KB 96 KB 0 KB
syscomments 132 72 KB 56 KB 16 KB 0 KB
Suppliers 29 72 KB 24 KB 48 KB 0 KB
sysobjects 100 64 KB 16 KB 48 KB 0 KB
sysreferences 13 56 KB 8 KB 48 KB 0 KB
sysusers 12 56 KB 8 KB 48 KB 0 KB
sysdepends 399 48 KB 16 KB 32 KB 0 KB
sysfilegroups 1 40 KB 8 KB 32 KB 0 KB
systypes 26 40 KB 8 KB 32 KB 0 KB
Territories 53 32 KB 8 KB 24 KB 0 KB
Region 4 32 KB 8 KB 24 KB 0 KB
EmployeeTerritories 49 32 KB 8 KB 24 KB 0 KB
Shippers 3 24 KB 8 KB 16 KB 0 KB
syspermissions 53 24 KB 8 KB 16 KB 0 KB
sysfiles1 2 16 KB 8 KB 8 KB 0 KB
sysforeignkeys 0 0 KB 0 KB 0 KB 0 KB
sysfulltextcatalogs 0 0 KB 0 KB 0 KB 0 KB
sysfulltextnotify 0 0 KB 0 KB 0 KB 0 KB
sysfiles 0 0 KB 0 KB 0 KB 0 KB
CustomerCustomerDemo 0 0 KB 0 KB 0 KB 0 KB
CustomerDemographics 0 0 KB 0 KB 0 KB 0 KB
sysproperties 0 0 KB 0 KB 0 KB 0 KB
sysprotects 0 0 KB 0 KB 0 KB 0 KB
sysindexkeys 0 0 KB 0 KB 0 KB 0 KB
sysmembers 0 0 KB 0 KB 0 KB 0 KB
name rows reserved data index_size unused
------------------------------ ----------- ------------------ ------------------ ------------------ ------------------
TOTAL 4580 2440 KB 920 KB 1272 KB 248 KB
Notice the total rows & bytes used are also displayed. Additionally, you can pass a wildcarded parameter to limit the output to certain tables:
sp_spaceused_all 'Ord%'
This produces:
name rows reserved data index_size unused
------------------------------ ----------- ------------------ ------------------ ------------------ ------------------
Orders 830 504 KB 160 KB 320 KB 24 KB
Order Details 2155 328 KB 72 KB 208 KB 48 KB
name rows reserved data index_size unused
------------------------------ ----------- ------------------ ------------------ ------------------ ------------------
TOTAL 2985 832 KB 232 KB 528 KB 72 KB
Here's the creation script. We sort our output by space used so the biggest
tables are first -- but it could easily be changed to sort by # of rows or
table name.
CREATE procedure sp_spaceused_all
@TablePattern varchar(128) = '%',
@updateusage varchar(5) = false -- Param. for specifying that
-- usage info. should be updated.
as
--declare @TablePattern varchar(128)
--declare @updateusage varchar(5)
--set @TablePattern = '%'
--set @updateusage = 'false' -- Param. for specifying that
declare @id int -- The object id of @objname.
declare @type character(2) -- The object type.
declare @pages int -- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @objname nvarchar(776) -- The object we want size on (DWV Mod)
/*Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
id int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
/*
** Check to see if user wants usages updated.
*/
if @updateusage is not null
begin
select @updateusage=lower(@updateusage)
if @updateusage not in ('true','false')
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
select @dbname = db_name()
declare cur cursor for
select TABLE_NAME = convert(sysname,o.name) /* make nullable */
from sysobjects o
where o.type in ('U','S') --and permissions (o.id)&4096 <> 0
and convert(sysname,o.name) like @TablePattern
order by 1
OPEN cur
FETCH NEXT FROM cur INTO @objname
/*
** Check to see that the objname is local.
*/
WHILE @@FETCH_STATUS = 0
BEGIN
--print 'processing ' + @objname
/*
** Try to find the object.
*/
select @id = null
select @id = id, @type = xtype
from sysobjects
where id = object_id(@objname)
/*
** Does the object exist?
*/
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end
/*
** Update usages if user specified to do so.
*/
if @updateusage = 'true'
dbcc updateusage(0,@objname) with no_infomsgs
set nocount on
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (id, reserved)
select @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where id = @id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where id = @id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where id = @id
update #spt_space
set rows = i.rows
from #spt_space s
join sysindexes i on i.id = s.id
where i.indid < 2
and i.id = @id
FETCH NEXT FROM cur INTO @objname
END
CLOSE Cur
DEALLOCATE cur
select name = substring(object_name(id), 1, 30),
rows = convert(char(11), rows),
reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'KB')
from #spt_space s, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by s.reserved * d.low desc
select name = cast('TOTAL' as char(30)),
rows = convert(char(11), SUM(rows)),
reserved = ltrim(str(SUM(reserved * d.low) / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(SUM(data * d.low) / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(SUM(indexp * d.low) / 1024.,15,0) +
' ' + 'KB'),
unused = ltrim(str(SUM(unused * d.low) / 1024.,15,0) +
' ' + 'KB')
from #spt_space s, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
return (0)
For More Information
- What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
- The Best SQL Server Web Links: tips, tutorials, scripts, and more.
- Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
