How to get size of the database in SQL / SQL Azure?

Some of the case database exceeds the db size and got error while trying to insert new row into the db. Most of the case this will happen after some years of using database. So we cannot identify the issue very quickly and may be our application need to shut down for some days. To monitoring this issue we can create a user interface (if we needed) which showing currently used size of the database.

Query for getting size of the database in SQL / SQL Azure

To get the table wise size we can use following query, which returns the name of the tables with size of the table.

select    
      sys.objects.name, sum(reserved_page_count) * 8.0 / 1024 as size_in_MB 
from    
      sys.dm_db_partition_stats, sys.objects 
where    
      sys.dm_db_partition_stats.object_id = sys.objects.object_id
group by sys.objects.name

To get the entire database used size we can use following query.

select
      sum(reserved_page_count) * 8.0 / 1024 as size_in_MB
from
      sys.dm_db_partition_stats
GO