How to Find Out Total Disk Free Space For SQL Server DBs disks using T-SQL

How to Find Out Total Disk Space For DBs disks using T-SQL for SQL Sever with Automatic Version Detiction



 

if
left(cast(serverproperty('productversion') as varchar) , 4) in('10.5','11.0','12.0','13.0')

begin

--2008 R2 SP1

select left(cast(serverproperty('productversion') as varchar) , 4) as SQLServerVersion,A.name, volume_mount_point

-- , (cast(total_bytes as decimal(19,2))/1024/1024/1024)as total_Disk_Capacity_GB --original

, cast((cast(total_bytes as decimal(19,2))/1024/1024/1024)as decimal(19,2)) as total_Disk_Capacity_GB --rounded

-- , (cast(available_bytes as decimal(19,2))/1024/1024/1024)as available_Space_GB --original

, cast((cast(available_bytes as decimal(19,2))/1024/1024/1024)as decimal(19,2)) as available_Space_GB --rounded

from sys.master_files A

CROSS APPLY sys.dm_os_volume_stats(A.database_id, A.file_id) --all DBs files

--CROSS APPLY sys.dm_os_volume_stats(db_id(A.name), A.file_id) --for the current DBs

where (A.file_id=1 or A.file_id=2)and A.database_id >4

end

else


begin

--2008 or earlier

IF OBJECT_ID('tempdb..#TMP_DRIVES') is not Null

DROP TABLE #TMP_DRIVES

CREATE TABLE #TMP_DRIVES (DRIVE CHAR(1) NOT NULL

,MBFREE INT NOT NULL)

INSERT INTO #TMP_DRIVES

EXEC xp_fixeddrives





select
left(cast(serverproperty('productversion') as varchar) , 4)as SQLServerVersion

,DRIVE as Volum_mount_drive

,convert(decimal(19,2),(cast(MBFREE as decimal(19,2))/1024 )) as Available_Space_GB

--,name

from #TMP_DRIVES a--, sys.master_files b

where DRIVE in(select LEFT(physical_name,1) from sys.master_files where database_id>4)

drop table #TMP_DRIVES

end