欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

查询数据库大小的代码

程序员文章站 2022-03-22 12:55:04
SELECT a.name [文件名称] ,cast(a.[size]*1.0/128 as decimal(12,1)) AS [文件设置大小(MB)] ,CAST( fileproperty(s.name,'SpaceUsed')/(8*16.0) AS DECIMAL(12,1)) AS [文... ......
select a.name [文件名称]  
    ,cast(a.[size]*1.0/128 as decimal(12,1)) as [文件设置大小(mb)]  
    ,cast( fileproperty(s.name,'spaceused')/(8*16.0) as decimal(12,1)) as [文件所占空间(mb)]  
    ,cast( (fileproperty(s.name,'spaceused')/(8*16.0))/(s.size/(8*16.0))*100.0  as decimal(12,1)) as [所占空间率%]  
    ,case when a.growth =0 then '文件大小固定,不会增长' else '文件将自动增长' end [增长模式]  
    ,case when a.growth > 0 and is_percent_growth = 0 then '增量为固定大小'  
        when a.growth > 0 and is_percent_growth = 1 then '增量将用整数百分比表示'  
        else '文件大小固定,不会增长' end as [增量模式]  
    ,case when a.growth > 0 and is_percent_growth = 0 then cast(cast(a.growth*1.0/128as decimal(12,0)) as varchar)+'mb'  
        when a.growth > 0 and is_percent_growth = 1 then cast(cast(a.growth as decimal(12,0)) as varchar)+'%'  
        else '文件大小固定,不会增长' end as [增长值(%或mb)]  
    ,a.physical_name as [文件所在目录]  
    ,a.type_desc as [文件类型]  
from sys.database_files  a  
inner join sys.sysfiles as s on a.[file_id]=s.fileid  
left join sys.dm_db_file_space_usage b on a.[file_id]=b.[file_id]