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.