December 17, 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 [email protected]+' ;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.

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

Hope you enjoyed reading the post!

#SQL Server
  • Pingback: How to Retrieve the File Size used by a SQL Server Database on Hard Disk()

  • Perfect. Thank you very much.

Support us!

If you like this site please help and make click on any of these buttons!

Powered by WordPress Popup