数据库大小分布情况 - MSSQL教程

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

查看本栏目更多精彩内容：http://www.bianceng.cn/database/SQLServer/

（编辑：应用网_镇江站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!