Locating table's size 2

[djhawthorn]

Is there a query (or a way) to find out how big each table inside a database is?

I have a 17.6GB database, which I believe is mostly made up of log files. I want to find out which table inside the database is taking up the space though, as there are some 50 tables stored there.

MCSE NT4/W2K

 

[mrdenny]

One way in Enterprise Manager, select the database. Right click on the database, select view, select taskpad. In the right hand pane select the tables tab. It will have the size of each table, and all the indexes.

You can also use the sp_spaceused to find the space used for an object.

sp_spaceused '{ObjectName}'

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[jester777]

Try this bit of code. Collects info about each user table in the database and sorts the output into largest table first.

declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
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

-- Create 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

/* Code from sp_spaceused */
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


/* 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 objid = @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 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(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(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

 

[djhawthorn]

Thanks both.
The taskpad view is exactly what I was after; the transaction log is 17GB ! Data is only 600MB.

I assume its safe to delete this file every so often? Or restrict its growth?

I'm guessing it's exactly what it says it is - a log of every transaction (SQL call) made against the database yeah?

Silly questions probably, but I havent done an SQL Admin course, and I'd rather hate to delete something valuable by mistake.

MCSE NT4/W2K

 

[mrdenny]

ok, don't delete the file. With the SQL Server running you won't be able to delete the file anyway.

Backup the transaction log and the data will be deleted from it automatically. You can then shrink the log down to a more normal size. You'll want to backup the log on a regular basis.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)