数据库大小分布情况

 

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

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