How to Retrieve File Size of all Databases Present in SQL Server on Hard Disk

In this post we will write a query that will return the file sizes of all the databases present on a SQL Server.

Code below :

CREATE TABLE #db_space
(
[DBname] NVARCHAR(50),
[Fileid] NVARCHAR(10),
[Filegroup] NVARCHAR(10),
[TotalExtents] int,
[UsedExtents] int,
[Name] NVARCHAR(50),
[FileName] NVARCHAR(300),
);
GO
DECLARE @name sysname
DECLARE CUR cursor for SELECT [name] FROM sys.databases
OPEN CUR
FETCH CUR INTO @name
WHILE @@fetch_status = 0
BEGIN
     BEGIN TRAN
     INSERT INTO #db_space([Fileid],[Filegroup],[TotalExtents],[UsedExtents],[Name],[FileName])
     EXEC('USE '+@name+' ;DBCC SHOWFILESTATS;');
     COMMIT TRAN
     BEGIN TRAN
     UPDATE #db_space SET [DBname] = @name WHERE [DBname] is NULL;
     COMMIT TRAN
     FETCH CUR INTO @name
END
CLOSE CUR
DEALLOCATE CUR
GO
SELECT
DBName, [TotalExtents]/16 AS [Total Size (in MB)], [UsedExtents]/16 AS [Used Size (in MB)],  FileName

FROM #db_space ORDER BY [DBname]
GO
DROP TABLE #db_space
GO

Running this query on SQL Server will return an output similar to the below screen.