求知当前数据库的空间使用大小

欲知道当前数据库空间使用大小,可以从下面这个系统表sys.database_files 得到。

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Insus.NET
-- Create date: 2019-05-14
-- Update date: 2019-05-14
-- Description: 数据库空间使用
-- =============================================
CREATE FUNCTION [dbo].[svf_DB_SpaceUsed]()
RETURNS @db TABLE([name] SYSNAME,[physical_name] SYSNAME,[type_desc] SYSNAME,[Size(byte)] bigint)
AS
BEGIN   
    INSERT INTO @db ([name],[physical_name],[type_desc],[Size(byte)]) 
    SELECT [name],[physical_name],[type_desc],SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)  
    FROM sys.database_files    
    GROUP BY [name],[physical_name],[type_desc]
    RETURN
END

GO
Source Code

 

运行函数:

 

以上算出大小单位为byte,如果你需要MB或GB作为单位呢?

MB:

SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)/1024/1024

GB:

SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)/1024/1024/1024


当然还可以算出其它相关单位的值。

 

posted @ 2019-05-14 20:18 Insus.NET 阅读(...) 评论(...) 编辑 收藏