SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Database Table Sizes?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mitin
Yak Posting Veteran

81 Posts

Posted - 05/02/2013 :  12:01:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 05/02/2013 :  12:27:33  Show Profile  Reply with Quote
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

USA
337 Posts

Posted - 05/02/2013 :  13:15:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 05/02/2013 :  14:15:21  Show Profile  Visit jackv's Homepage  Reply with Quote
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

USA
798 Posts

Posted - 05/03/2013 :  13:33:03  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000