17/12/2012 by Nitesh

How to Retrieve File size of all Databases present in SQL Server on Hard Disk

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.