查看表空间

SELECT
        0 AS Warning
        ,a.[file_id]
        ,a.name [文件名称]
        ,isnull(g.groupname,N'不适用') AS [文件组]
        ,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.max_size=-1 THEN N'无限制' ELSE cast(cast(a.max_size/128 as int) as varchar(30)) END AS [限制大小(MB)]
        ,CASE WHEN A.growth =0 THEN N'文件大小固定，不会增长' ELSE N'文件将自动增长' end [增长模式]
        ,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN N'增量为固定大小'
            WHEN A.growth > 0 AND is_percent_growth = 1 THEN N'增量将用整数百分比表示'
            ELSE N'文件大小固定，不会增长' 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 N'文件大小固定，不会增长' 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]
    LEFT JOIN sysfilegroups AS g ON s.groupid=g.groupid
    ORDER BY a.[type]