SQL: List of database files

The script below 1
can be used to extract a list of databases along with the files associated with each database. The size (according to SQL Server) is also returned in pages and bytes.

USE master
GO

DECLARE @dbID INT
DECLARE @name VARCHAR(500)
DECLARE @sql VARCHAR(1000)

CREATE TABLE #databasefiles
( DatabaseID INT,
  DatabaseName VARCHAR(100),
  Filename VARCHAR(500),
  SizePages INT,
  SizeBytes INT
)
 
DECLARE dbCursor CURSOR FAST_FORWARD 
FOR
      SELECT dbid, name
      FROM Master.dbo.sysdatabases
      WHERE name NOT IN ('master', 'msdb', 'tempdb', 'model')
      ORDER BY name
OPEN dbCursor
FETCH NEXT FROM dbCursor INTO @dbID, @name
WHILE @@FETCH_STATUS = 0
BEGIN
      SET @sql = 'INSERT INTO #databasefiles (DatabaseID, DatabaseName, Filename, SizePages, SizeBytes) '
      SET @sql = @sql + 'SELECT ' + str(@dbid) + ' AS dbid, ''' + @name + ''', filename, size, size * 8 '
      SET @sql = @sql + 'FROM [' + @name + '].dbo.sysfiles'
      EXEC (@sql)
      FETCH NEXT FROM dbCursor INTO @dbID, @name
END

CLOSE dbCursor
DEALLOCATE dbCursor

SELECT *
FROM #databasefiles

DROP TABLE #databasefiles
  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.