Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    119

    Unanswered: Finding out physical size of table

    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

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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

  3. #3
    Join Date
    Jan 2006
    Posts
    119
    Quote Originally Posted by MCrowley View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •