Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Database Table Sizes?

Author  Topic 

mitin
Yak Posting Veteran

81 Posts

Posted - 2013-05-02 : 12:01:13
Hi,

How can I get a simple breakdown of the size of my database in terms of GB/MB per table?

Is there a simple sql command that can be run? or is there an easy way to do this through the GUI?

I wish to delete from some of the larger tables in my DB, but I can find out which ones those are at the moment. I know if I right click the DB name and then go to properties I get the overall size, but how do I see size of each table?

Thank you guys :)
mitin

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-02 : 12:27:33
You can use this query (which is from here: http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database )
SELECT 
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, p.Rows
ORDER BY
t.Name
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-05-02 : 13:15:07
If you are in SSMS then you can select the columns to viewn in "Object Explorer Details" which include "Data Space Used (KB)" and "Size (MB)".
Right click on the header (where "Name" is) and you can select the column to view.

djj
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-05-02 : 14:15:21
To calculate table size with existing data
http://www.sqlserver-dba.com/2011/08/calculate-table-size-with-existing-data.html

To get table sizes and percentages of total
http://www.sqlserver-dba.com/2010/04/table-sizes-and-percentages.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2013-05-03 : 13:33:03
quote:
Originally posted by djj55

If you are in SSMS then you can select the columns to viewn in "Object Explorer Details" which include "Data Space Used (KB)" and "Size (MB)".
Right click on the header (where "Name" is) and you can select the column to view.

djj



You should also include Index Space Used and Row Count. I have this setup and it is very useful for getting a quick look at this data. You can also used the standard report 'Disk Usage by Table' which you can get to by right-clicking the database and selecting reports.
Go to Top of Page
   

- Advertisement -