sqlserver数据库获取数据库信息

各位用户为了找寻关于sqlserver数据库获取数据库信息的资料费劲了很多周折。这里教程网为您整理了关于sqlserver数据库获取数据库信息的相关资料，仅供查阅，以下为您介绍关于sqlserver数据库获取数据库信息的详细内容

MS SQL SERVER 获取当前数据库文件等信息，适用于多个版本：

代码如下: 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) * 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;

 

例如我们在某个Database上，执行结果是：

代码如下: XXX_standard_data    D:Program FilesSQLServer2012MSSQL11.MSSQLSERVERMSSQLDATAwolf_wanzheng.mdf    2000.00    1286.38    713.63    35.68    0    PRIMARY XXX_standard_log    D:Program FilesSQLServer2012MSSQL11.MSSQLSERVERMSSQLDATAwolf_wanzheng_2.ldf    157.19    2.47    154.72    98.43    0    NULL XXX_temp2_data    D:Program FilesSQLServer2012MSSQL11.MSSQLSERVERMSSQLDATAwolf_wanzheng_1.ndf    500.00    0.06    499.94    99.99    0    temp2  XXX_temp2_log    D:Program FilesSQLServer2012MSSQL11.MSSQLSERVERMSSQLDATAwolf_wanzheng_3.ldf    142.88    2.25    140.63    98.43    0    NULL