Viewing the disk space usage (size) of all the tables in a SQL Server 2005/2008 database

I spent a couple of hours searching for a script that returns the size of all the tables in a database. Most of the ones that you find use sp_msforeachtable but that is just for one table and you then need to use temporary tables to hold the values.
 
This article on the Database Journal by Ananth Kumar (AKA MAK) had this handy script that simply works by copy-pasting it as is. Read the linked article for details on viewing the size by schema.
 
BEGIN TRY 
SELECT 
    (row_number() over(order by a3.name, a2.name))%2 as l1,
    a3.name AS [schemaname],
    a2.name AS [tablename],
    a1.rows as row_count,
    (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved, 
    a1.data * 8 AS data,
    (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN 
    (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
    (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN 
    (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
 (SELECT 
  ps.object_id,
  SUM (
   CASE
    WHEN (ps.index_id < 2) THEN row_count
    ELSE 0
   END
   ) AS [rows],
  SUM (ps.reserved_page_count) AS reserved,
  SUM (
   CASE
     WHEN (ps.index_id < 2) THEN 
   (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
     ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
   END
   ) AS data,
  SUM (ps.used_page_count) AS used
 FROM sys.dm_db_partition_stats ps
 GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN 
 (SELECT 
  it.parent_id,
  SUM(ps.reserved_page_count) AS reserved,
  SUM(ps.used_page_count) AS used
  FROM sys.dm_db_partition_stats ps
  INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
  WHERE it.internal_type IN (202,204)
  GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> 'S' and a2.type <> 'IT'
ORDER BY a3.name, a2.name
END TRY 
BEGIN CATCH 
SELECT 
 -100 as l1
, 1 as schemaname 
,       ERROR_NUMBER() as tablename
,       ERROR_SEVERITY() as row_count
,       ERROR_STATE() as reserved
,       ERROR_MESSAGE() as data
,       1 as index_size
,   1 as unused 
END CATCH

 

Quick extra tip, if you export the result-set to Excel you can do additional sorts to figure out more stuff. For example the first table on this list was taking up 47GB of disk space in a 63 GB database. With the index taking more space than the actual data! But this is more of an OLAP db with just one batch update daily and optimized for retrieving data faster so I guess that’s okay.

sql-disk-space-usage

 

Viewing the disk space usage (size) of all the tables in a SQL Server 2005/2008 database

Went shooting

beretta

So we went clay shooting yesterday with Adam Tyler (an Athen’s Olympic Bronze medalist and two time Commonwealth Gold medalist) showing us how it’s done.

After playing Counter Strike with the guys at office it felt good too hold a real live Beretta shot gun. Got four of the first five shots in the first round.

Here’s one where I hit the clay.

good-shot

The Melbourne Unique World team!

UniqueWorld Team

Went shooting

Melbourne

It’s been nearly four months since I moved to Melbourne and having been in Sydney six months prior, Melbourne was indeed a welcome change.

Although it’s very easy to forget how beautiful it is here. My favorite part of the daily commute is the ten minute walk through Fawkner Park (named after Melbourne’s co-founder).

Here’s a bird’s eye view of Fawkner park.

Fawkner-Park

A few from today’s morning walk.

IMG_0662

 

IMG_0663

 

On sunny days we grab our lunch and head downstairs to have it at the park. It’s no wonder Melboune has consistently been at the top spot in the World’s Most Livable Cities list over the years.

Melbourne

Could it be true? Is Live Search (Kumo) really getting better than the great Goog?

So I wanted to find a tool that would do fast folder comparisons. Searching on Google gave me a lot of useless stuff most of them related to fast folder access or folder comparison but not ‘fast folder comparison’.

On a whim I switched live.com and lo and behold I was presented with UltraCompare in the search results with this to say:

Save time by performing an extremely fast folder comparison using just the sizes and timestamps of corresponding files.

Even a quoted search for “fast folder comparison” returns 8 results in Live but just 2 in Google.

Will I switch to Live search? No.

But something to keep in mind when I’m not getting the results I want on Google.

BTW: The Google Search Wiki is certainly intriguing. I totally dig neat Ajax stuff.

GooglePromote

Could it be true? Is Live Search (Kumo) really getting better than the great Goog?

A Personal Blog: Spaces vs Blogger

I’ve been wanting to start a personal blog for sometime and finally went ahead with it.

This post though is not about my personal blog but rather about which platform I chose. I’ve been blogging for nearly five years now and went the geek way and hosted my platform. But I’m now fed up with having to maintain the stuff myself whenever new versions are released.

I really wanted to use MSN Live Spaces since it had the nice integration with Messenger and the new Live bits that were going to be released excited me. But after spending a couple of hours tinkering with it I was finally put off by the obnoxious advertising. Not only were they flashy images, there were two of them a big banner as well as one on the right hand.

I was debating between Blogger and WordPress and then went ahead with Blogger as I could host it on my own domain. Other things that pushed me towards Google was that I already had my email hosted at Google, Feedburner integration.

On a side note, for those interested, my personal blog is at http://merill.blogspot.com until I get Custom Domains with Yahoo sorted out. But hey Blogger has permanent redirects so there’s nothing to worry. Really.

A Personal Blog: Spaces vs Blogger