資料庫大小分布情況

---資料庫大小分布情況

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