---數據庫大小分布情況
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
查看本欄目
