SQL

sql server query database space available

--DISK SPACE

DROP TABLE #tmp
CREATE TABLE #tmp (COL1 VARCHAR(20),COL2 INT)
INSERT INTO #tmp
EXEC master..xp_fixeddrives
SELECT col1 AS drive, col2/1000 AS free_spce_in_gb FROM #tmp ORDER BY 2

DROP TABLE #output
create table #output (output nvarchar(255) null)
insert #output (output) EXEC master..xp_cmdshell 'PowerShell.exe -noprofile -command "get-WmiObject Win32_LogicalDisk  | Format-Table -Property DeviceID,FreeSpace"'
select LEFT(output,3) AS DRIVE, LTRIM(RTRIM(RIGHT(OUTPUT,LEN(OUTPUT)-3))) AS FREE_SPACE from #output where output LIKE '%C:%'

--SERVER SPACE

SELECT DISTINCT 
	dovs.volume_mount_point AS Drive,
	DB_NAME(dovs.database_id) DBName,
	dovs.logical_volume_name AS LogicalName,
	(CONVERT(INT,dovs.available_bytes/1048576.0) / 1000) AS FreeSpaceInGB
FROM
	sys.master_files mf
	CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY 
	FreeSpaceInGB ASC;

--DB SPACE
SELECT DB_NAME() AS DbName, 
    name AS FileName, 
    type_desc,
    size/128.0 AS CurrentSizeMB,  
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);