数据库大小分布情况

数据库大小分布情况2014-03-14 csdn博客 ocpyang---数据库大小分布情况

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