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

欲知道当前数据库空间使用大小，可以从下面这个系统表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

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

 

转载于:https://www.cnblogs.com/insus/p/10864334.html