Size Distribution of databases

---database size distribution

SET NoCount on

CREATE TABLE #DBsize

(

[DatabaseName] [nvarchar] (a) Not NULL,

[Size] [Decimal] Not NULL,

[Name] [nvarchar] (a) Not NULL,

[Filename] [nvarchar] (+) 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 server name,

Count (DISTINCT RTRIM (DatabaseName as VARCHAR)) as database number,

Drive as [total data space used],

CAST (SUM (size) as VARCHAR) as [total size (MB)],

CAST (Mbfree as VARCHAR) as [remaining size (MB)]

From #DBsize

INNER JOIN #drives on left (#DBsize. Filename, 1) = #drives. Drive

GROUP by Drive,

Mbfree

SELECT RTRIM (CAST (DatabaseName as VARCHAR ()) as [database name],

Drive as [disk],

filename as [filename],

CAST (size as INT) as [Size (MB)],

CAST (Mbfree as VARCHAR) as [remaining size (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 [disk character],

[Remaining size (MB)] DESC

DROP TABLE #DBsize

DROP TABLE #drives

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/