T-SQL query to get database size and table size (in Gigabytes)
July 12, 2013 § 9 Comments
Please find below T-SQL query to find DB size and Tables Size (In GB) along with their row count. Please share your feedback if you like this post!!! 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
Happy Coding 🙂
not accurate, there is a difference in row count when checked with select count(*) from table_name or sp_spaceused.
Thanks Asir, for finding and letting me know the mistake.
thanks!!
awesome, just what I needed, thanks.
Awesome!!! Thank you so much!
its returning duplicate rows when i filed on my db
Hi,
Can you share your t-sql query here in the remarks and t-sql query along with the snapshot of your resultset to my email id:
gaurava16fc@gmail.com
Thanks!
–To find non partition tables and tables size in descending order
create table #TableSize (
[Name] varchar(255),
[Rows] int,
[Reserved] varchar(255),
[Data] varchar(255),
[Index_Size] varchar(255),
[Unused] varchar(255))
create table #ConvertedSizes (
[Name] varchar(255),
–[Rows] int,
[Table_Space_GB] int
)
EXEC sp_MSforeachtable @Command1=”Insert into #TableSize
EXEC sp_spaceused ‘?'”
Insert into #ConvertedSizes ([Name]
— [Rows]
, Table_Space_GB
)
select [Name], –[Rows],
Convert(INT,SUBSTRING(reserved, 0, LEN(reserved)-2))/(1024*1024) as Table_Space_GB
from #TableSize
select DB_Name() as DatabaseName, [Name]
–, [Rows]
, Table_Space_GB
from #ConvertedSizes
Where Name IN (
Select ‘[‘ + s.name + ‘].[‘ + t.name + ‘]’ As Name
From sys.tables t
Inner Join sys.schemas s
On t.schema_id = s.schema_id
Inner Join sys.partitions p
on p.object_id = t.object_id
Where p.index_id In (0, 1)
Group By ‘[‘ + s.name + ‘].[‘ + t.name + ‘]’, s.name, t.name
Having Count(*) = 1
)
order by Table_Space_GB desc
drop table #TableSize
drop table #ConvertedSizes
Hi Harsh,
Thanks for commenting out to my post, but I can hardly see any difference apart from the GB Size, which is not accurate. For example, if it is less than 1 GB then it is showing the ZERO (0) there…
Can you please make me understand what difference you are making with this query, if I have missed anything?
Thanks!