For one of my clients, I needed to know and document not only database names, size and locations but also disk volume information as well.
Now there is an easier way to get the same info using a DMV.
https://sqlpal.blogspot.com/2018/04/get-sql-server-database-size-location.html
Here is another way to retrieve same information using OLE functions.
-- SQL SERVER VERSION 2005 & UP
/*
Caveat: This relies on OLE functions, if that option is not enalbed (which is default) it will try to enable it
*/
-- 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
