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

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

 

运行函数：

 

以上算出大小单位为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

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