T-SQL query to get database size and table size (in GBs)

Hi Friends,

Hope, you are doing good...!

Please find below T-SQL query to find DB size and Tables Size (In GB) 
along with their row count. 

T-SQL query to get Database size in GB:
SELECT 
  dbs.NAME, 
  CAST((SUM(mFiles.SIZE) * 8 / 1024.00 / 1024.00) AS NUMERIC(18,2)) 
      AS [DB SIZE (In GB)]
FROM
   SYS.MASTER_FILES mFiles INNER JOIN SYS.DATABASES dbs
      ON dbs.DATABASE_ID = mFiles.DATABASE_ID
WHERE dbs.DATABASE_ID > 4 
             -- FILTER OUT THE DATABSES AS "master", 
             -- "tempdb", "model" AND "msdb"
GROUP BY dbs.NAME
ORDER BY [DB SIZE (In GB)]

T-SQL query to get tables size in GB with no of rows:

USE <Database_Name_To_Be_Used>
GO
SELECT 
 s.name + '.' + t.Name AS [Table Name],
 part.rows AS [Total Rows In Table - Modified],
 CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3)) 
 AS [Table's Total Space In GB]
FROM 
 SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
 INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id
 INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id 
                    AND idx.Index_id = part.Index_id
 INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id
 INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id
 INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id
WHERE t.Is_ms_shipped = 0 AND idx.Object_id > 255 
GROUP BY t.Name, s.name, part.rows
ORDER BY [Table's Total Space In GB] DESC

Original post is published here at my primary blog:
https://gauravlal.wordpress.com/2013/07/12/t-sql-query-to-get-database-size-and-table-size-in-gigabytes/
Please share your feedback if you like this post!!!





Happy Coding :-):-)