数据库大小分布情况

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

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