关闭

数据库大小分布情况

10060人阅读 评论(0) 收藏 举报
 

---数据库大小分布情况

SET NoCount ON
CREATE TABLE #DBsize
    (
      [DatabaseName] [nvarchar](75) NOT NULL ,
      [Size] [decimal] NOT NULL ,
      [Name] [nvarchar](75) NOT NULL ,
      [Filename] [nvarchar](300) NOT NULL
    )

CREATE TABLE #drives
    (
      [Drive] [char](5) NOT NULL ,
      [MBFree] [decimal] NOT NULL
    )

INSERT  INTO #DBsize
        EXEC sp_MSforeachdb 'Select ''?'' as DatabaseName, Case When [?]..sysfiles.size * 8 / 1024 = 0 Then 1 Else [?]..sysfiles.size * 8 / 1024 End
AS size,[?]..sysfiles.name,
[?]..sysfiles.filename From [?]..sysfiles'


INSERT  INTO #drives
        EXEC xp_fixeddrives


SELECT  @@Servername AS 服务器名 ,
        COUNT(DISTINCT RTRIM(CAST(DatabaseName AS VARCHAR(75)))) AS 数据库数目 ,
        Drive AS [使用的总数据空间] ,
        CAST(SUM(Size) AS VARCHAR(10)) AS [总大小(MB)] ,
        CAST(MBFree AS VARCHAR(10)) AS [剩余大小(MB)]
FROM    #DBsize
        INNER JOIN #drives ON LEFT(#DBsize.Filename, 1) = #drives.Drive
GROUP BY Drive ,
        MBFree

 

SELECT  RTRIM(CAST(DatabaseName AS VARCHAR(75))) AS [数据库名] ,
        Drive AS [盘符] ,
        Filename AS [文件名] ,
        CAST(Size AS INT) AS [大小(MB)] ,
        CAST(MBFree AS VARCHAR(10)) AS [剩余大小(MB)]
FROM    #DBsize
        INNER JOIN #drives ON LEFT(#DBsize.Filename, 1) = #drives.Drive
GROUP BY DatabaseName ,
        Drive ,
        MBFree ,
        Filename ,
        CAST(Size AS INT)
ORDER BY [盘符] ,
        [剩余大小(MB)] DESC


DROP TABLE #DBsize
DROP TABLE #drives

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1320541次
    • 积分:15010
    • 等级:
    • 排名:第799名
    • 原创:249篇
    • 转载:1篇
    • 译文:0篇
    • 评论:1条
    博客专栏