Menu

Want to work with us? We're on the lookout for digital experts.

We're hiring

SQL Server Code to Find Size of Tables Within a Database

Web Bureau


31 January 2011 by Web Bureau

Just run this piece of code to see table size within a database:

select 'Database Name: ', db_name()
 
set nocount on
 
if exists(select name from tempdb..sysobjects where name='##tmp')
 
drop table ##tmp
 
create table ##tmp(nam varchar(50), rows int, res varchar(15),data varchar(15),ind_sze varchar(15),unsed varchar(15))
 
go
 
declare @tblname varchar(50)
 
declare tblname CURSOR for select name from sysobjects where xtype='U'
 
open tblname
 
Fetch next from tblname into @tblname
 
WHILE @@FETCH_STATUS = 0
 
BEGIN
 
insert into ##tmp
 
exec sp_spaceused @tblname
 
  FETCH NEXT FROM tblname INTO @tblname
 
END
 
CLOSE tblname
 
deallocate tblname
 
go
 
select nam Table_Name,rows Total_Rows,res Total_Table_Size,data Data_size,ind_sze Index_Size,unsed Unused_Space from ##tmp order by total_table_size DESC
 
drop table ##tmp
 

Grow your businessStart a project with us today.

This site uses essential cookies for parts of the site to operate and have already been set. Find out more about how we use cookies and how you may delete them. You may delete cookies, but parts of the site will not work.