Loading

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

Om Agarwal

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

Comments

Join the conversation! Your thoughts help the community grow.

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