Ms SQL SERVER gets information such as the current database file for multiple versions:
Copy codeThe Code is as follows:
SELECT dbf. file_id AS FileID
, Dbf. name AS [FileName]
, S. filename AS FileLocation
, CAST (dbf. size/128.0 as decimal (19,2) AS FileSizeMB
, CAST (FILEPROPERTY (dbf. name, 'spaceused ') AS int)/128.0 as decimal (19,2) AS SpaceUsedMB
, CAST (dbf. size/128.0-CAST (FILEPROPERTY (dbf. name, 'spaceused ') AS int)/128.0 as decimal () AS AvailableSpaceMB
, CAST (dbf. size/128.0-(FILEPROPERTY (dbf. name, 'spaceused ')/128.0)/(dbf. size/128.0) * 100 as decimal (19,2) AS [% FreeSpace]
Dbf. growth/128 AS FileGrowthMB
, F. name AS FilegroupName
FROM sys. database_files dbf
Inner join sys. sysfiles s ON dbf. name = s. name
Left join sys. filegroups f ON dbf. data_space_id = f. data_space_id
Order by dbf. name;
For example, on a Database, the execution result is:
Copy codeThe Code is as follows:
XXX_standard_data
D: \ Program Files \ SQLServer2012 \ MSSQL11.MSSQLSERVER \ MSSQL \ DATA \ wolf_wanzheng.mdf 2000.00 1286.38 713.63 35.68 0 PRIMARY
XXX_standard_log
D: \ Program Files \ SQLServer2012 \ MSSQL11.MSSQLSERVER \ MSSQL \ DATA \ wolf_wanzheng_2.ldf 157.19 2.47 154.72 0 NULL
XXX_temp2_data
D: \ Program Files \ SQLServer2012 \ MSSQL11.MSSQLSERVER \ MSSQL \ DATA \ wolf_wanzheng_1.ndf 500.00 0.06 499.94 99.99 0 temp2
XXX_temp2_log
D: \ Program Files \ SQLServer2012 \ MSSQL11.MSSQLSERVER \ MSSQL \ DATA \ wolf_wanzheng_3.ldf 142.88 2.25 140.63 0 NULL