MS SQL SERVER obtains information such as the current database file and is available for multiple versions:
Copy Code code 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 (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 (19,2)) as Availablespacemb
, CAST ((dbf.size/128.0-(Fileproperty (dbf.name, ' spaceused ')/128.0))/(dbf.size/128.0) * 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 results are:
Copy Code code 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 98.43 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 98.43 0 NULL