Friends,

In our previous post, we read about how can we get the file size of a SQL Server database 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.

Hope you enjoyed reading the post!