June 4, 2007 at 7:51 pm
Hi
How to check the size of all the tables in a database
Thanks
June 4, 2007 at 9:36 pm
execute this query. It gives you all the detail about the tables in the database
declare @id int
declare @type char(2)
declare @pages int
declare @dbname sysname
declare @bytesperpage dec(15,0)
declare @pagespermb dec(15,0)
create table #spt_space
(objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null)
set nocount on
-- now creating a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'
open c_tables
fetch next from c_tables into @id
while @@fetch_status = 0
begin
insert into #spt_space (objid,reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0,1,255) and id = @id
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 objid = @id
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0,1,255)and id = @id) - data
where objid = @id
update #spt_space
set unused = reserved - (select sum(used)
from sysindexes
where indid in (0,1,255) and id = @id)
where objid = @id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id
fetch next from c_tables into @id
end
select tablename = (select left(name,60) from sysobjects where id = objid),
rows = convert(char(11), rows),
reservedkb = ltrim(rtrim(str(reserved * d.low/1024.,15,0) + ' ' + 'KB')),
datakb = ltrim(str(data * d.low/1024.,15,0) + ' ' + 'KB' ),
indexsizekb = ltrim(str(indexp * d.low/1024.,15,0) + ' ' + 'KB'),
unusedkb = ltrim(rtrim(str(unused * d.low/1024.,15,0 ) + '' + 'KB'))
from #spt_space,master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables
June 5, 2007 at 1:06 am
exec sp_spaceused MyTable
---------------------------------------
elsasoft.org
June 5, 2007 at 3:02 am
or even
exec sp_msforeachtable 'sp_spaceused ''?'' '
{note: two single quotes around the ?}
David
If it ain't broke, don't fix it...
June 5, 2007 at 6:33 pm
excellent. Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy