This article is replaced by:
Nowadays, there is an easier way to get the same info using DMVs.
https://sqlpal.blogspot.com/2018/04/get-sql-server-database-size-location.html
But for (very) older versions of SQL Server where the DMVs may not be available, here is another way to retrieve same information using OLE functions.
Note: This relies on OLE functions, if that option is not enalbed (which is default) it will try to enable it
SET NOCOUNT ON -- if OLE automation is not enabled, execute the following code to enable it -- Enabling Ole Automation Procedures exec sp_configure 'show advanced options', 1; GO RECONFIGURE; GO exec sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO SET NOCOUNT ON DECLARE @SQL VARCHAR(8000) DECLARE @hr int, @fso int, @drive char(1), @odrive int, @TotalSize varchar(20), @FreeSize varchar(20), @VolumeName varchar(4000), @MB Numeric SET @MB = 1048576 IF OBJECT_ID('TEMPDB..#t1_DBSpaceInfo') IS NOT NULL DROP TABLE #t1_DBSpaceInfo IF OBJECT_ID('TEMPDB..#drives') IS NOT NULL DROP TABLE #drives CREATE TABLE #drives (drive char(1) PRIMARY KEY, VolumeName varchar(4000), FreeSpace int NULL, TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso DECLARE dcur CURSOR LOCAL FAST_FORWARD FOR SELECT drive from #drives ORDER by drive OPEN dcur FETCH NEXT FROM dcur INTO @drive WHILE @@FETCH_STATUS=0 BEGIN EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive EXEC @hr = sp_OAGetProperty @odrive,'VolumeName', @VolumeName OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive UPDATE #drives SET TotalSize=@TotalSize/@MB, VolumeName = @VolumeName WHERE drive=@drive FETCH NEXT FROM dcur INTO @drive End Close dcur DEALLOCATE dcur EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso SELECT ID=IDENTITY(int,1,1) , SERVERPROPERTY('MachineName') SQLServer, SERVERPROPERTY('InstanceName') InstanceName, DB_NAME(database_id) as DBName, name as LogicalFileName, CASE (type_desc) When 'LOG' Then 'Log' When 'ROWS' Then 'Data' ELSE type_desc END AS FileType, VolumeName, LEFT(UPPER(physical_name),1) AS Drive, databasepropertyex(DB_NAME(database_id), 'IsFulltextEnabled') IsFulltextEnabled, databasepropertyex(DB_NAME(database_id), 'IsPublished') IsPublished, cast(CAST((size*8.0/1024.00)AS numeric(10,2)) as INT) AS [SizeinMB], TotalSize / 1024 ActualVolSizeGB, FreeSpace / 1024 ActualVolFreeGB, CAST(FILEPROPERTY(name,'spaceused')/128.00 as numeric(10,2)) as [UsedSpaceMB], Growth, data_space_id, physical_name as filename INTO #t1_DBSpaceInfo FROM sys.master_files mf INNER JOIN #drives dr on LEFT(UPPER(mf.physical_name),1) = UPPER(dr.drive) ORDER BY LEFT(UPPER(mf.physical_name),1), DB_NAME(database_id) set @SQL = ' SELECT SQLServer, InstanceName, DBName, LogicalFileName LogicalName, filename PhysicalFileName, FileType, VolumeName, Drive, IsFulltextEnabled, IsPublished, [SizeinMB], case when ID = (SELECT MAX(ID) FROM #t1_DBSpaceInfo WHERE DRIVE = t1.DRIVE) then CAST((SELECT SUM(SizeInMB) from #t1_DBSpaceInfo where DRIVE = t1.DRIVE) AS VARCHAR(50)) else '''' END VolumeSize, case when ID = (SELECT MAX(ID) FROM #t1_DBSpaceInfo WHERE DRIVE = t1.DRIVE) then CAST(ActualVolSizeGB as varchar(50)) else '''' END ActualVolSizeGB , case when ID = (SELECT MAX(ID) FROM #t1_DBSpaceInfo WHERE DRIVE = t1.DRIVE) then CAST(ActualVolFreeGB as varchar(50)) else '''' END ActualVolFreeGB FROM #t1_DBSpaceInfo t1 ORDER BY ID' PRINT @SQL EXEC(@SQL) IF OBJECT_ID('TEMPDB..#drives') IS NOT NULL DROP TABLE #drives IF OBJECT_ID('TEMPDB..#t1_DBSpaceInfo') IS NOT NULL DROP TABLE #t1_DBSpaceInfo