blocks|key|270747|text|我不知道你所说的效率是什么意思，但这很简单，它对我很有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|270748|SELECT
++++DB_NAME(db.database_id)+DatabaseName,
++++(CAST(mfrows.RowSize+AS+FLOAT)*8)/1024+RowSizeMB,
++++(CAST(mflog.LogSize+AS+FLOAT)*8)/1024+LogSizeMB,
++++(CAST(mfstream.StreamSize+AS+FLOAT)*8)/1024+StreamSizeMB,
++++(CAST(mftext.TextIndexSize+AS+FLOAT)*8)/1024+TextIndexSizeMB
FROM+sys.databases+db
++++LEFT+JOIN+(SELECT+database_id,+SUM(size)+RowSize+FROM+sys.master_files+WHERE+type+=+0+GROUP+BY+database_id,+type)+mfrows+ON+mfrows.database_id+=+db.database_id
++++LEFT+JOIN+(SELECT+database_id,+SUM(size)+LogSize+FROM+sys.master_files+WHERE+type+=+1+GROUP+BY+database_id,+type)+mflog+ON+mflog.database_id+=+db.database_id
++++LEFT+JOIN+(SELECT+database_id,+SUM(size)+StreamSize+FROM+sys.master_files+WHERE+type+=+2+GROUP+BY+database_id,+type)+mfstream+ON+mfstream.database_id+=+db.database_id
++++LEFT+JOIN+(SELECT+database_id,+SUM(size)+TextIndexSize+FROM+sys.master_files+WHERE+type+=+4+GROUP+BY+database_id,+type)+mftext+ON+mftext.database_id+=+db.database_id|code-block|syntax|javascript|270749|结果如下：|270750|DatabaseName++RowSizeMB+LogSizeMB+StreamSizeMB+TextIndexSizeMB
-------------+---------+---------+------------+---------------
master++++++++4+++++++++1.25++++++NULL+++++++++NULL
model+++++++++2.25++++++0.75++++++NULL+++++++++NULL
msdb++++++++++14.75+++++8.1875++++NULL+++++++++NULL
tempdb++++++++8+++++++++0.5+++++++NULL+++++++++NULL|270751|注:灵感来自this+article|offset|length|270752|entityMap|0|LINK|mutability|MUTABLE|url|http://blog.sqlauthority.com/2010/02/08/sql-server-find-the-size-of-database-file-find-the-size-of-log-file/^0|0|0|0|0|6|C|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

I don't know exactly what you mean by efficiency but this is straightforward and it works for me:



<pre><code>SELECT
 DB_NAME(db.database_id) DatabaseName,
 (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
 (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
 (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
 (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
 LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
 LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
 LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id
 LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id
</code></pre>

With results like:

<pre><code>DatabaseName RowSizeMB LogSizeMB StreamSizeMB TextIndexSizeMB
------------- --------- --------- ------------ ---------------
master 4 1.25 NULL NULL
model 2.25 0.75 NULL NULL
msdb 14.75 8.1875 NULL NULL
tempdb 8 0.5 NULL NULL
</code></pre>

Note: was inspired by <a href="http://blog.sqlauthority.com/2010/02/08/sql-server-find-the-size-of-database-file-find-the-size-of-log-file/">this article</a>

blocks|key|269258|text|with+fs
as
(
++++select+database_id,+type,+size+*+8.0+/+1024+size
++++from+sys.master_files
)
select+
++++name,
++++(select+sum(size)+from+fs+where+type+=+0+and+fs.database_id+=+db.database_id)+DataFileSizeMB,
++++(select+sum(size)+from+fs+where+type+=+1+and+fs.database_id+=+db.database_id)+LogFileSizeMB
from+sys.databases+db|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|269259|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>with fs
as
(
 select database_id, type, size * 8.0 / 1024 size
 from sys.master_files
)
select 
 name,
 (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
 (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db
</code></pre>

blocks|key|267189|text|使用总数据库大小排序描述|type|unstyled|depth|inlineStyleRanges|entityRanges|data|267190|SELECT+++++
DB_NAME(db.database_id)+DatabaseName,+++++
(CAST(mfrows.RowSize+AS+FLOAT)*8)/1024+RowSizeMB,+++++
(CAST(mflog.LogSize+AS+FLOAT)*8)/1024+LogSizeMB,+
(CAST(mfrows.RowSize+AS+FLOAT)*8)/1024/1024%2B(CAST(mflog.LogSize+AS+FLOAT)*8)/1024/1024+DBSizeG,
(CAST(mfstream.StreamSize+AS+FLOAT)*8)/1024+StreamSizeMB,+++++
(CAST(mftext.TextIndexSize+AS+FLOAT)*8)/1024+TextIndexSizeMB+
FROM+sys.databases+db+++++
LEFT+JOIN+(SELECT+database_id,+
++++++++++++++++++SUM(size)+RowSize+
++++++++++++FROM+sys.master_files+
++++++++++++WHERE+type+=+0+
++++++++++++GROUP+BY+database_id,+type)+mfrows+
++++ON+mfrows.database_id+=+db.database_id+++++
LEFT+JOIN+(SELECT+database_id,+
++++++++++++++++++SUM(size)+LogSize+
++++++++++++FROM+sys.master_files+
++++++++++++WHERE+type+=+1+
++++++++++++GROUP+BY+database_id,+type)+mflog+
++++ON+mflog.database_id+=+db.database_id+++++
LEFT+JOIN+(SELECT+database_id,+
++++++++++++++++++SUM(size)+StreamSize+
++++++++++++++++++FROM+sys.master_files+
++++++++++++++++++WHERE+type+=+2+
++++++++++++++++++GROUP+BY+database_id,+type)+mfstream+
++++ON+mfstream.database_id+=+db.database_id+++++
LEFT+JOIN+(SELECT+database_id,+
++++++++++++++++++SUM(size)+TextIndexSize+
++++++++++++++++++FROM+sys.master_files+
++++++++++++++++++WHERE+type+=+4+
++++++++++++++++++GROUP+BY+database_id,+type)+mftext+
++++ON+mftext.database_id+=+db.database_id+
+++++++ORDER+BY+4+DESC|code-block|syntax|javascript|267191|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

with Total Database size ordered Desc

<pre><code>SELECT 
DB_NAME(db.database_id) DatabaseName, 
(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB, 
(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB, 
(CAST(mfrows.RowSize AS FLOAT)*8)/1024/1024+(CAST(mflog.LogSize AS FLOAT)*8)/1024/1024 DBSizeG,
(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB, 
(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB 
FROM sys.databases db 
LEFT JOIN (SELECT database_id, 
 SUM(size) RowSize 
 FROM sys.master_files 
 WHERE type = 0 
 GROUP BY database_id, type) mfrows 
 ON mfrows.database_id = db.database_id 
LEFT JOIN (SELECT database_id, 
 SUM(size) LogSize 
 FROM sys.master_files 
 WHERE type = 1 
 GROUP BY database_id, type) mflog 
 ON mflog.database_id = db.database_id 
LEFT JOIN (SELECT database_id, 
 SUM(size) StreamSize 
 FROM sys.master_files 
 WHERE type = 2 
 GROUP BY database_id, type) mfstream 
 ON mfstream.database_id = db.database_id 
LEFT JOIN (SELECT database_id, 
 SUM(size) TextIndexSize 
 FROM sys.master_files 
 WHERE type = 4 
 GROUP BY database_id, type) mftext 
 ON mftext.database_id = db.database_id 
 ORDER BY 4 DESC
</code></pre>

blocks|key|2967668|text|SELECT
++++DB.name,
++++SUM(CASE+WHEN+type+=+0+THEN+MF.size+*+8+/+1024+ELSE+0+END)+AS+DataFileSizeMB,
++++SUM(CASE+WHEN+type+=+1+THEN+MF.size+*+8+/+1024+ELSE+0+END)+AS+LogFileSizeMB
FROM
++++sys.master_files+MF
++++JOIN+sys.databases+DB+ON+DB.database_id+=+MF.database_id
GROUP+BY+DB.name
ORDER+BY+DataFileSizeMB+DESC|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2967669|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT
 DB.name,
 SUM(CASE WHEN type = 0 THEN MF.size * 8 / 1024 ELSE 0 END) AS DataFileSizeMB,
 SUM(CASE WHEN type = 1 THEN MF.size * 8 / 1024 ELSE 0 END) AS LogFileSizeMB
FROM
 sys.master_files MF
 JOIN sys.databases DB ON DB.database_id = MF.database_id
GROUP BY DB.name
ORDER BY DataFileSizeMB DESC
</code></pre>

blocks|key|267228|text|我最近在这个页面上看到了类似这样的东西。以防有人遇到这种情况，并且只对核心用户数据库感兴趣，您可以使用类似这样的内容，它不包括Master、msdb……|type|unstyled|depth|inlineStyleRanges|entityRanges|data|267229|SELECT
DB_NAME(db.database_id)+DatabaseName,
(CAST(mfrows.RowSize+AS+FLOAT)*8)/1024+RowSizeMB,
(CAST(mflog.LogSize+AS+FLOAT)*8)/1024+LogSizeMB
FROM+sys.databases+db
LEFT+JOIN+(SELECT+database_id,+SUM(size)+RowSize+FROM+sys.master_files+WHERE+type+=+0+GROUP+BY+database_id,+type)+mfrows+ON+mfrows.database_id+=+db.database_id
LEFT+JOIN+(SELECT+database_id,+SUM(size)+LogSize+FROM+sys.master_files+WHERE+type+=+1+GROUP+BY+database_id,+type)+mflog+ON+mflog.database_id+=+db.database_id
where+DB_NAME(db.database_id)+not+like+'master'
and+DB_NAME(db.database_id)+not+like+'msdb'
and+DB_NAME(db.database_id)+not+like+'model'
and+DB_NAME(db.database_id)+not+like+'tempdb'
and+DB_NAME(db.database_id)+not+like+'Northwind'
and+DB_NAME(db.database_id)+not+like+'ReportServer'
order+by+DB_NAME(db.database_id)|code-block|syntax|javascript|267230|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I recently came across this page looking for something like this.
Just in case someone comes across this and is interested in only the core user databases, you can use something like this which excludes Master, msdb...

<pre><code>SELECT
DB_NAME(db.database_id) DatabaseName,
(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB
FROM sys.databases db
LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
where DB_NAME(db.database_id) not like 'master'
and DB_NAME(db.database_id) not like 'msdb'
and DB_NAME(db.database_id) not like 'model'
and DB_NAME(db.database_id) not like 'tempdb'
and DB_NAME(db.database_id) not like 'Northwind'
and DB_NAME(db.database_id) not like 'ReportServer'
order by DB_NAME(db.database_id)
</code></pre>

blocks|key|272842|text|这是一个简单、快速和可靠的查询，它将在一个漂亮、易于阅读的输出中给出所有数据库和日志文件的名称、大小以及数据库状态(例如在线)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|272843|SELECT
++++D.name,
++++F.Name+AS+FileType,
++++F.physical_name+AS+PhysicalFile,
++++F.state_desc+AS+OnlineStatus,
++++CAST(F.size+AS+bigint)+*+8*1024+AS+SizeInBytes,
++++CAST((F.size*8.0)/1024/1024+AS+decimal(18,3))+AS+SizeInGB
FROM+
++++sys.master_files+F
++++INNER+JOIN+sys.databases+D+ON+D.database_id+=+F.database_id
ORDER+BY+SizeInBytes+desc|code-block|syntax|javascript|272844|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here's a simple, quick and reliable query that will give all database and log file names, sizes and also database statuses (e.g. ONLINE) in a nice, easy to read output:

<pre><code>SELECT
 D.name,
 F.Name AS FileType,
 F.physical_name AS PhysicalFile,
 F.state_desc AS OnlineStatus,
 CAST(F.size AS bigint) * 8*1024 AS SizeInBytes,
 CAST((F.size*8.0)/1024/1024 AS decimal(18,3)) AS SizeInGB
FROM 
 sys.master_files F
 INNER JOIN sys.databases D ON D.database_id = F.database_id
ORDER BY SizeInBytes desc
</code></pre>

blocks|key|2967753|text|IF+OBJECT_ID('tempdb.dbo.#space')+IS+NOT+NULL
++++DROP+TABLE+#space

CREATE+TABLE+#space+(
++++++database_id+INT+PRIMARY+KEY
++++,+data_used_size+DECIMAL(18,2)
++++,+log_used_size+DECIMAL(18,2)
)

DECLARE+@SQL+NVARCHAR(MAX)

SELECT+@SQL+=+STUFF((
++++SELECT+'
++++USE+['+%2B+d.name+%2B+']
++++INSERT+INTO+#space+(database_id,+data_used_size,+log_used_size)
++++SELECT
++++++++++DB_ID()
++++++++,+SUM(CASE+WHEN+[type]+=+0+THEN+space_used+END)
++++++++,+SUM(CASE+WHEN+[type]+=+1+THEN+space_used+END)
++++FROM+(
++++++++SELECT+s.[type],+space_used+=+SUM(FILEPROPERTY(s.name,+''SpaceUsed'')+*+8.+/+1024)
++++++++FROM+sys.database_files+s
++++++++GROUP+BY+s.[type]
++++)+t;'
++++FROM+sys.databases+d
++++WHERE+d.[state]+=+0
++++FOR+XML+PATH(''),+TYPE).value('.',+'NVARCHAR(MAX)'),+1,+2,+'')

EXEC+sys.sp_executesql+@SQL

SELECT
++++++d.database_id
++++,+d.name
++++,+d.state_desc
++++,+d.recovery_model_desc
++++,+t.total_size
++++,+t.data_size
++++,+s.data_used_size
++++,+t.log_size
++++,+s.log_used_size
FROM+(
++++SELECT
++++++++++database_id
++++++++,+log_size+=+CAST(SUM(CASE+WHEN+[type]+=+1+THEN+size+END)+*+8.+/+1024+AS+DECIMAL(18,2))
++++++++,+data_size+=+CAST(SUM(CASE+WHEN+[type]+=+0+THEN+size+END)+*+8.+/+1024+AS+DECIMAL(18,2))
++++++++,+total_size+=+CAST(SUM(size)+*+8.+/+1024+AS+DECIMAL(18,2))
++++FROM+sys.master_files
++++GROUP+BY+database_id
)+t
JOIN+sys.databases+d+ON+d.database_id+=+t.database_id
LEFT+JOIN+#space+s+ON+d.database_id+=+s.database_id
ORDER+BY+t.total_size+DESC|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2967754|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
 DROP TABLE #space

CREATE TABLE #space (
 database_id INT PRIMARY KEY
 , data_used_size DECIMAL(18,2)
 , log_used_size DECIMAL(18,2)
)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
 SELECT '
 USE [' + d.name + ']
 INSERT INTO #space (database_id, data_used_size, log_used_size)
 SELECT
 DB_ID()
 , SUM(CASE WHEN [type] = 0 THEN space_used END)
 , SUM(CASE WHEN [type] = 1 THEN space_used END)
 FROM (
 SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
 FROM sys.database_files s
 GROUP BY s.[type]
 ) t;'
 FROM sys.databases d
 WHERE d.[state] = 0
 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

SELECT
 d.database_id
 , d.name
 , d.state_desc
 , d.recovery_model_desc
 , t.total_size
 , t.data_size
 , s.data_used_size
 , t.log_size
 , s.log_used_size
FROM (
 SELECT
 database_id
 , log_size = CAST(SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
 , data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
 , total_size = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))
 FROM sys.master_files
 GROUP BY database_id
) t
JOIN sys.databases d ON d.database_id = t.database_id
LEFT JOIN #space s ON d.database_id = s.database_id
ORDER BY t.total_size DESC
</code></pre>

blocks|key|3181627|text|所有这些看起来都太复杂了！还是我错过了什么？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3181628|当然，你所需要的就是：|3181629|select+d.name,+case+when+m.type+=+0+then+'Data'+else+'Log'+end,++m.size+*+8+/+1024
from+sys.master_files+m+JOIN+sys.databases+d+ON+d.database_id+=+m.database_id|code-block|syntax|javascript|3181630|或者如果你不想要日志：|3181631|select+d.name,+m.size+*+8+/+1024
from+sys.master_files+m+JOIN+sys.databases+d+ON+d.database_id+=+m.database_id+and+m.type+=0|3181632|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

All seem overly complicated! Or am I missing something?

Surely all you need is something like:

<pre><code>select d.name, case when m.type = 0 then 'Data' else 'Log' end, m.size * 8 / 1024
from sys.master_files m JOIN sys.databases d ON d.database_id = m.database_id
</code></pre>

or if you don't want the log:

<pre><code>select d.name, m.size * 8 / 1024
from sys.master_files m JOIN sys.databases d ON d.database_id = m.database_id and m.type =0
</code></pre>

blocks|key|3181653|text|请找到更多细节或从下面的链接https://gallery.technet.microsoft.com/SIZE-OF-ALL-DATABASES-IN-0337f6d5#content下载脚本|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3181654|+DECLARE+@spacetable+table
+(
+database_name+varchar(50)+,
+total_size_data+int,
+space_util_data+int,
+space_data_left+int,
+percent_fill_data+float,
+total_size_data_log+int,
+space_util_log+int,
+space_log_left+int,
+percent_fill_log+char(50),
+[total+db+size]+int,
+[total+size+used]+int,
+[total+size+left]+int
+)
+insert+into++@spacetable
+EXECUTE+master.sys.sp_MSforeachdb+'USE+[?];
+select+x.[DATABASE+NAME],x.[total+size+data],x.[space+util],x.[total+size+data]-x.[space+util]+[space+left+data],
+x.[percent+fill],y.[total+size+log],y.[space+util],
+y.[total+size+log]-y.[space+util]+[space+left+log],y.[percent+fill],
+y.[total+size+log]%2Bx.[total+size+data]+''total+db+size''
+,x.[space+util]%2By.[space+util]+''total+size+used'',
+(y.[total+size+log]%2Bx.[total+size+data])-(y.[space+util]%2Bx.[space+util])+''total+size+left''
++from+(select+DB_NAME()+''DATABASE+NAME'',
+sum(size*8/1024)+''total+size+data'',sum(FILEPROPERTY(name,''SpaceUsed'')*8/1024)+''space+util''
+,case+when+sum(size*8/1024)=0+then+''less+than+1%25+used''+else
+substring(cast((sum(FILEPROPERTY(name,''SpaceUsed''))*1.0*100/sum(size))+as+CHAR(50)),1,6)+end+''percent+fill''
+from+sys.master_files+where+database_id=DB_ID(DB_NAME())++and++type=0
+group+by+type_desc++)+as+x+,
+(select+
+sum(size*8/1024)+''total+size+log'',sum(FILEPROPERTY(name,''SpaceUsed'')*8/1024)+''space+util''
+,case+when+sum(size*8/1024)=0+then+''less+than+1%25+used''+else
+substring(cast((sum(FILEPROPERTY(name,''SpaceUsed''))*1.0*100/sum(size))+as+CHAR(50)),1,6)+end+''percent+fill''
+from+sys.master_files+where+database_id=DB_ID(DB_NAME())++and++type=1
+group+by+type_desc++)y'
+select+*+from+@spacetable
+order+by+database_name|code-block|syntax|javascript|3181655|entityMap|0|LINK|mutability|MUTABLE|url|https://gallery.technet.microsoft.com/SIZE-OF-ALL-DATABASES-IN-0337f6d5#content^0|E|27|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

please find more deatils or download the script from below link 
 <a href="https://gallery.technet.microsoft.com/SIZE-OF-ALL-DATABASES-IN-0337f6d5#content" rel="noreferrer">https://gallery.technet.microsoft.com/SIZE-OF-ALL-DATABASES-IN-0337f6d5#content</a>

<pre><code> DECLARE @spacetable table
 (
 database_name varchar(50) ,
 total_size_data int,
 space_util_data int,
 space_data_left int,
 percent_fill_data float,
 total_size_data_log int,
 space_util_log int,
 space_log_left int,
 percent_fill_log char(50),
 [total db size] int,
 [total size used] int,
 [total size left] int
 )
 insert into @spacetable
 EXECUTE master.sys.sp_MSforeachdb 'USE [?];
 select x.[DATABASE NAME],x.[total size data],x.[space util],x.[total size data]-x.[space util] [space left data],
 x.[percent fill],y.[total size log],y.[space util],
 y.[total size log]-y.[space util] [space left log],y.[percent fill],
 y.[total size log]+x.[total size data] ''total db size''
 ,x.[space util]+y.[space util] ''total size used'',
 (y.[total size log]+x.[total size data])-(y.[space util]+x.[space util]) ''total size left''
 from (select DB_NAME() ''DATABASE NAME'',
 sum(size*8/1024) ''total size data'',sum(FILEPROPERTY(name,''SpaceUsed'')*8/1024) ''space util''
 ,case when sum(size*8/1024)=0 then ''less than 1% used'' else
 substring(cast((sum(FILEPROPERTY(name,''SpaceUsed''))*1.0*100/sum(size)) as CHAR(50)),1,6) end ''percent fill''
 from sys.master_files where database_id=DB_ID(DB_NAME()) and type=0
 group by type_desc ) as x ,
 (select 
 sum(size*8/1024) ''total size log'',sum(FILEPROPERTY(name,''SpaceUsed'')*8/1024) ''space util''
 ,case when sum(size*8/1024)=0 then ''less than 1% used'' else
 substring(cast((sum(FILEPROPERTY(name,''SpaceUsed''))*1.0*100/sum(size)) as CHAR(50)),1,6) end ''percent fill''
 from sys.master_files where database_id=DB_ID(DB_NAME()) and type=1
 group by type_desc )y'
 select * from @spacetable
 order by database_name
</code></pre>

blocks|key|3181678|text|有时安全问题会阻止请求所有的数据库，您需要使用db前缀逐个查询，对于这些情况，我创建了这个动态查询|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3181679|go
declare+@Results+table+([Name]+nvarchar(max),+[DataFileSizeMB]+int,+[LogFileSizeMB]+int);

declare+@QaQuery+nvarchar(max)
declare+@name+nvarchar(max)

declare+MY_CURSOR+cursor+
++local+static+read_only+forward_only
for+
select+name+from+master.dbo.sysdatabases+where+name+not+in+('master',+'tempdb',+'model',+'msdb',+'rdsadmin');

open+MY_CURSOR
fetch+next+from+MY_CURSOR+into+@name
while+@@FETCH_STATUS+=+0
begin+
++++if(len(@name)>0)
++++begin
++++++++print+@name+%2B+'+Column+Exist'
++++++++set+@QaQuery+=+N'select+
++++++++++++++++++++++++++++'''%2B@name%2B'''+as+Name
++++++++++++++++++++++++++++,sum(case+when+type+=+0+then+size+else+0+end)+as+DataFileSizeMB
++++++++++++++++++++++++++++,sum(case+when+type+=+1+then+size+else+0+end)+as+LogFileSizeMB
++++++++++++++++++++++++from+['%2B@name%2B'].sys.database_files
++++++++++++++++++++++++group+by+replace(name,+''_log'',+'''')';

++++++++insert+@Results+exec+sp_executesql+@QaQuery;
++++end
++fetch+next+from+MY_CURSOR+into+@name
end
close+MY_CURSOR
deallocate+MY_CURSOR

select+*+from+@Results+order+by+DataFileSizeMB+desc
go|code-block|syntax|javascript|3181680|entityMap^0|2|2|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

sometimes SECURITY issues prevent from asking for all the db's and you need to query one by one with the db prefix, for those cases i created this dynamic query

<pre><code>go
declare @Results table ([Name] nvarchar(max), [DataFileSizeMB] int, [LogFileSizeMB] int);

declare @QaQuery nvarchar(max)
declare @name nvarchar(max)

declare MY_CURSOR cursor 
 local static read_only forward_only
for 
select name from master.dbo.sysdatabases where name not in ('master', 'tempdb', 'model', 'msdb', 'rdsadmin');

open MY_CURSOR
fetch next from MY_CURSOR into @name
while @@FETCH_STATUS = 0
begin 
 if(len(@name)&gt;0)
 begin
 print @name + ' Column Exist'
 set @QaQuery = N'select 
 '''+@name+''' as Name
 ,sum(case when type = 0 then size else 0 end) as DataFileSizeMB
 ,sum(case when type = 1 then size else 0 end) as LogFileSizeMB
 from ['+@name+'].sys.database_files
 group by replace(name, ''_log'', '''')';

 insert @Results exec sp_executesql @QaQuery;
 end
 fetch next from MY_CURSOR into @name
end
close MY_CURSOR
deallocate MY_CURSOR

select * from @Results order by DataFileSizeMB desc
go
</code></pre>

blocks|key|3181701|text|不是窃取你的答案并将其改写为点或任何东西，但这里是另一个因式分解：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3181702|select+d.name,+
++++sum(m0.size*8.0/1024)+data_file_size_mb,+
++++sum(m1.size*8.0/1024)+log_file_size_mb+
from+sys.databases+d
inner+join+sys.master_files+m0+on+m0.database_id+=+d.database_id
inner+join+sys.master_files+m1+on+m1.database_id+=+d.database_id
where+m0.type+=+0+and+m1.type+=+1
group+by+d.name,+d.database_id
order+by+d.database_id|code-block|syntax|javascript|3181703|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Not to steal your answer and adapt it for points or anything, but here is another factorization:

<pre><code>select d.name, 
 sum(m0.size*8.0/1024) data_file_size_mb, 
 sum(m1.size*8.0/1024) log_file_size_mb 
from sys.databases d
inner join sys.master_files m0 on m0.database_id = d.database_id
inner join sys.master_files m1 on m1.database_id = d.database_id
where m0.type = 0 and m1.type = 1
group by d.name, d.database_id
order by d.database_id
</code></pre>

blocks|key|2967871|text|一个更好、更简单的方法|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2967872|SELECT+[Database+Name]+=+DB_NAME(database_id),
+++++[Type]+=+CASE+WHEN+Type_Desc+=+'ROWS'+THEN+'Data+File(s)'
+++++++++++++++WHEN+Type_Desc+=+'LOG'++THEN+'Log+File(s)'
+++++++++++++++ELSE+Type_Desc+END,
+++++[Size+in+MB]+=+CAST(+((SUM(Size)*+8)+/+1024.0)+AS+DECIMAL(18,2)+)
FROM+++sys.master_files
--Uncomment+if+you+need+to+query+for+a+particular+database
--WHERE++++++database_id+=+DB_ID(‘Database+Name’)+
GROUP+BY++GROUPING+SETS
++++++++(
+++++++++++++++(DB_NAME(database_id),+Type_Desc),
+++++++++++++++(DB_NAME(database_id))
++++++++)+ORDER+BY++++++DB_NAME(database_id),+Type_Desc+DESC|code-block|syntax|javascript|2967873|它将分别提供数据文件和日志文件的大小，如下所示|2967874|DatabaseName++++Type++++++++++++Size+in+MB
-------------------------------------------
FMS+++++++++++++Data+File(s)++++23.00
FMS+++++++++++++Log+File(s)+++++1.50
PointOfSale+++++Data+File(s)++++4.00
PointOfSale+++++Log+File(s)+++++1.25
Union2++++++++++Data+File(s)++++336.00
Union2++++++++++Log+File(s)+++++1191.13
SurveyProject+++Data+File(s)++++4.00
SurveyProject+++Log+File(s)+++++1.00|2967875|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

A better and quite simpler one 

<pre><code>SELECT [Database Name] = DB_NAME(database_id),
 [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
 WHEN Type_Desc = 'LOG' THEN 'Log File(s)'
 ELSE Type_Desc END,
 [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )
FROM sys.master_files
--Uncomment if you need to query for a particular database
--WHERE database_id = DB_ID(‘Database Name’) 
GROUP BY GROUPING SETS
 (
 (DB_NAME(database_id), Type_Desc),
 (DB_NAME(database_id))
 ) ORDER BY DB_NAME(database_id), Type_Desc DESC
</code></pre>

It will give you size of Data File(s) and Log File(s) separately like below

<pre><code>DatabaseName Type Size in MB
-------------------------------------------
FMS Data File(s) 23.00
FMS Log File(s) 1.50
PointOfSale Data File(s) 4.00
PointOfSale Log File(s) 1.25
Union2 Data File(s) 336.00
Union2 Log File(s) 1191.13
SurveyProject Data File(s) 4.00
SurveyProject Log File(s) 1.00
</code></pre>

blocks|key|2967910|text|下面的代码对我来说工作得很好。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2967911|++++SELECT
++++++++D.name+As+DbName,
++++++++F.Name+AS+FullDbName,
++++++++CASE+WHEN+F.type_desc='ROWS'+THEN+'mdf'+ELSE+'ldf'+END+AS+FileType,
++++++++F.physical_name+AS+PhysicalFile,
++++++++CONVERT(DATE,D.create_date)+AS+CreationDate,
++++++++F.state_desc+AS+OnlineStatus,
++++++++CAST((F.size*8)/1024+AS+VARCHAR(26))+%2B+'+MB'+AS+FileSize_MB,
++++++++CAST(F.size*8+AS+VARCHAR(32))+%2B+'+Bytes'+AS+FileSize_Bytes,
++++++++CAST(CAST(ROUND((F.size*8)/(1024.0*1024.0),0)+AS+INT)+AS+VARCHAR(32))+%2B+'+GB'+AS+FileSize_GB

++++FROM+
++++++++sys.master_files+F
++++++++INNER+JOIN+sys.databases+D+ON+D.database_id+=+F.database_id

++++ORDER+BY
+++++++++D.name+|code-block|syntax|javascript|2967912|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The following code worked for me very well.

<pre><code> SELECT
 D.name As DbName,
 F.Name AS FullDbName,
 CASE WHEN F.type_desc='ROWS' THEN 'mdf' ELSE 'ldf' END AS FileType,
 F.physical_name AS PhysicalFile,
 CONVERT(DATE,D.create_date) AS CreationDate,
 F.state_desc AS OnlineStatus,
 CAST((F.size*8)/1024 AS VARCHAR(26)) + ' MB' AS FileSize_MB,
 CAST(F.size*8 AS VARCHAR(32)) + ' Bytes' AS FileSize_Bytes,
 CAST(CAST(ROUND((F.size*8)/(1024.0*1024.0),0) AS INT) AS VARCHAR(32)) + ' GB' AS FileSize_GB

 FROM 
 sys.master_files F
 INNER JOIN sys.databases D ON D.database_id = F.database_id

 ORDER BY
 D.name 
</code></pre>

blocks|key|270898|text|简化和改进版本：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|270899|SELECT
++++D.name,
++++CAST(SUM(F.size)+AS+bigint)+*+8*1024+AS+SizeInBytes,
++++CAST(SUM(F.size*8.0)/1024/1024+AS+decimal(18,3))+AS+SizeInGB
FROM+
++++sys.master_files+F
++++INNER+JOIN+sys.databases+D+ON+D.database_id+=+F.database_id
GROUP+BY+D.name
ORDER+BY+SizeInBytes+desc|code-block|syntax|javascript|270900|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Simplified &amp; Improved version:

<pre><code>SELECT
 D.name,
 CAST(SUM(F.size) AS bigint) * 8*1024 AS SizeInBytes,
 CAST(SUM(F.size*8.0)/1024/1024 AS decimal(18,3)) AS SizeInGB
FROM 
 sys.master_files F
 INNER JOIN sys.databases D ON D.database_id = F.database_id
GROUP BY D.name
ORDER BY SizeInBytes desc
</code></pre>

blocks|key|272935|text|所有这些示例在我的大多数数据库服务器上都工作得很好。但是，如果您有一台具有多个实例的服务器，并且所有这些服务器的查询都不会给出任何结果。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|272936|例如，第一个查询给出如下结果：|272937|name++++DataFileSizeMB++LogFileSizeMB
master++NULL++++NULL
tempdb++NULL++++NULL
model+++NULL++++NULL
msdb++++NULL++++NULL|code-block|syntax|javascript|272938|因此，数据库是从sys.databases中选择的，但是表sys.master_files似乎为空或没有给出任何结果。|offset|length|style|CODE|272939|即使是像select+*+from+sys.master_files这样的简单查询，结果也是0条记录。您没有收到任何错误，但没有找到任何记录。在我的服务器上，只有一个数据库实例，这可以很好地工作。|272940|entityMap^0|0|0|0|8|D|T|G|0|4|U|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@$K|W|L|X|M|N]|$K|Y|L|Z|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|10|8|@$K|11|L|12|M|N]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|R|$]]

All this examples work fine on most of my database servers.
However if you have 1 server with multiple instances and all those servers those query's give you no result.
For instance the first query gives a result like:
<pre><code>name DataFileSizeMB LogFileSizeMB
master NULL NULL
tempdb NULL NULL
model NULL NULL
msdb NULL NULL
</code></pre>
So the databases are selected from <code>sys.databases</code>, however the table <code>sys.master_files</code> seems to be empty or gives no result.
Even a simple query as <code>select * from sys.master_files</code> has a result of 0 records.
You don't receive any errors but no records are found.
On my servers with only 1 database instance this works fine.

I have MS SQL 2008 R2, 500 databases.
What is the most efficient, easiest and 'modern' way to query all databases sizes.

The output should have columns:

<ul>
<li>DatabaseName</li>
<li>DataFilesSize</li>
<li>LogFilesSize</li>
</ul>

SQL Server 2008: How to query all databases sizes?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有MSSQL2008 R2,500个数据库。查询所有数据库大小的最有效、最简单和最“现代”的方法是什么？输出应包含列：DatabaseNameDataFilesSizeLogFilesSize

问SQL Server 2008:如何查询所有数据库大小？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server 2008:如何查询所有数据库大小？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server 2008:如何查询所有数据库大小？
EN