Compatible from SQL Server 2008
Returns size of all databases all database files with its location and state.
SELECT
@@SERVERNAME AS InstanceName,
db.name AS DatabaseName,
mf.Name AS FileType,
mf.physical_name AS PhysicalFile,
mf.state_desc AS FileStatus,
(mf.size*8)/1024 AS FileSizeMB,
mf.size*8 AS SizeInBytes
FROM
sys.master_files mf
INNER JOIN sys.databases db ON db.database_id = mf.database_id
ORDER BY db.name
Results
Script results screen:
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| InstanceName | sysname | Name of SQL Server instance |
| DatabaseName | sysname | Name of database. |
| FileName | sysname | Logical name of the file in the database. |
| PhysicalFile | nvarchar(260) | Operating-system file name. |
| FileStatus | nvarchar(60) | Description of the file state: ONLINE RESTORING RECOVERING RECOVERY_PENDING SUSPECT OFFLINE DEFUNCT |
| FileSizeMB | int | Size of given database file in MB. |