当前位置: 数据库>sqlserver
sqlserver获取数据库信息sql语句
来源: 互联网 发布时间：2014-08-29
本文导语: 在SQL SERVER中获取当前数据库文件等信息，适用于多个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(FILEPRO...
在SQL SERVER中获取当前数据库文件等信息，适用于多个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;
, 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
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
您可能感兴趣的文章:
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。