Finding out physical size of table

Breako · #1 (**permalink**) 01-26-12, 09:22

Hi,
Just wondering say if I have a table of 1,000,000 rows how I do find out what size this table is on disk?

And how do I find out the size of all tables on disc?

Thanks

MCrowley · #2 (**permalink**) 01-26-12, 09:32

this should get you started:

Code:

select object_name(p.object_id) as "Table", 
	p.index_id, 
	f.name, 
	sum(total_pages)/128 as "Size in MB",
	convert(varchar(10), getdate(), 101), 
	count(*) as partitions
from sys.partitions p join
	sys.allocation_units a on p.partition_id = a.container_id join
	sys.filegroups f on a.data_space_id = f.data_space_id
group by p.object_id, p.index_id, f.name
order by sum(total_pages)/128 desc

Breako · #3 (**permalink**) 01-26-12, 11:40

Quote:

Originally Posted by MCrowley

this should get you started:

Code:

select object_name(p.object_id) as "Table", 
	p.index_id, 
	f.name, 
	sum(total_pages)/128 as "Size in MB",
	convert(varchar(10), getdate(), 101), 
	count(*) as partitions
from sys.partitions p join
	sys.allocation_units a on p.partition_id = a.container_id join
	sys.filegroups f on a.data_space_id = f.data_space_id
group by p.object_id, p.index_id, f.name
order by sum(total_pages)/128 desc

Thanks another good one is..

EXEC sp_spaceused 'Tablename'

Just to get info on a specific table.

Internet Services

Web Development

Digital Marketing

Consumer Tech