Get all Database Files (data, logs) in SQL Server

In this blog. Today I am determining all database files (data, logs) size and growth in SQL Server below given Query. This query show file group of the all database in Existing server and we can find the location where it's located in system.

CREATE TABLE #temp

(

Databasename VARCHAR(100),

Name VARCHAR(1000),

Fileid INT,

Filename VARCHAR(500),

Filegroup VARCHAR(100),

Size VARCHAR(200),

Maxsize VARCHAR(300),

Growth VARCHAR(100),

Usage VARCHAR(100)

)

 

DECLARE @database VARCHAR(100)

DECLARE cur CURSOR FOR

SELECT name

FROM master..sysdatabases

 

OPEN cur

 

FETCH next FROM cur INTO @database

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC('USE '+@database +'

INSERT INTO #temp(name ,fileid , filename,filegroup ,size ,maxsize ,growth ,usage )

 EXEC sp_helpfile')

 

UPDATE #temp

SET Databasename = @database

WHERE Databasename IS NULL

 

FETCH next FROM cur INTO @database

END

 

CLOSE cur

 

DEALLOCATE cur

 

SELECT *

FROM #temp

 

DROP TABLE #temp 

Output