Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Possible to find out sizes of each table in db? 1

Status
Not open for further replies.

LucieLastic

Programmer
May 9, 2001
1,694
GB
hi

We have quite a large, live database (in excess of 37gig) and my boss would like to know how big (in MB) what each table is. Is it possible to find this information out? Some of the tables have millions of rows in them, so can't do any 'select count(*)...' either for an idea of sizes.

Thanks in advance
lou

 
Run this in Query Analyzer against the DB you need to get the info on.

Code:
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

Thanks

J. Kusch
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top