获取 SQL Server 中所有数据库大小的总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7122170/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get overall sum of all databases size in a SQL Server
提问by olmed0
I want to calculate how much space my databases are using in a server. I could use sp_spacefilesor query sys.databasestable but that would give me separate results for each database and I would have to copy that into an excel sheet and calculate the sum from there.
我想计算我的数据库在服务器中使用了多少空间。我可以使用sp_spacefiles或查询sys.databases表，但这会给我每个数据库的单独结果，我必须将其复制到 Excel 表中并从那里计算总和。
Is there a direct way of doing it in T-SQL?
在 T-SQL 中有直接的方法吗？
Thanks.
谢谢。
回答by JNK
You can query master.sys.master_files:
您可以查询master.sys.master_files：
SELECT CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) As UsedSpace
FROM master.sys.master_files
This will give you a total in GB.
这将为您提供以 GB 为单位的总数。
Sys.Master_filesis a server-wide view that lists every file in every DB. It's available from SQL Server 2005 onward.
Sys.Master_files是一个服务器范围的视图，列出了每个数据库中的每个文件。它从 SQL Server 2005 开始可用。
回答by Jennifer S
Here's an answer I found on SQLServerCentral.com. There are a couple different scripts provided by different users on this page.. Perhaps one of them would provide what you're looking for.
这是我在 SQLServerCentral.com 上找到的答案。此页面上的不同用户提供了几种不同的脚本。也许其中之一会提供您正在寻找的内容。
http://www.sqlservercentral.com/Forums/Topic670489-146-1.aspx
http://www.sqlservercentral.com/Forums/Topic670489-146-1.aspx
Here is one of the scripts from MANU-J:
这是来自 MANU-J 的脚本之一：
Create TABLE #db_file_information(
fileid integer
, theFileGroup integer
, Total_Extents integer
, Used_Extents integer
, db varchar(30)
, file_Path_name varchar(300))
-- Get the size of the datafiles
insert into #db_file_information
( fileid
, theFileGroup
, Total_Extents
, Used_Extents
, db
, file_Path_name )
exec sp_MSForEachDB 'Use ?; DBCC showfilestats'
-- add two columns to the temp table
alter table #db_file_information add PercentFree as
((Total_Extents-Used_Extents)*100/(Total_extents))
alter table #db_file_information add TotalSpace_MB as
((Total_Extents*64)/1024)
alter table #db_file_information add UsedSpace_MB as
((Used_Extents*64)/1024)
alter table #db_file_information add FreeSpace_MB as
((Total_Extents*64)/1024-(Used_Extents*64)/1024)
select * from #db_file_information
drop table #db_file_information
回答by Putnik
Just in case someone needs per-file calculation:
以防万一有人需要按文件计算：
select physical_name, size,
CONVERT(DECIMAL(10,2),(size * 8.00) / 1024.00) As UsedSpace
from master.sys.master_files
order by physical_name
The results are in MBytes
结果以兆字节为单位
