检查数据库大小，但排除系统数据库

Question

有人问我一个"简单任务"；提取我们所有DB(大约50个(的所有已用空间，但在每个实例中排除系统DB。下面的代码在where子句中出现错误。我忘了什么？

SELECT [Database Name] = DB_NAME(database_id),
[Size in MB] = CONCAT(CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) ) AS VARCHAR(20)),' MB') AS [database_size]
FROM   sys.master_files

WHERE      database_id NOT IN (‘master’, ‘tempdev’,’tempdb’……etc………)

GROUP BY      GROUPING SETS
(
(DB_NAME(database_id), Type_Desc),
(DB_NAME(database_id))
)
ORDER BY      DB_NAME(database_id), Type_Desc DESC
GO

Answer 1

尝试以下查询，您会得到准确的输出，根据您的要求在条件下添加数据库名称

SELECT [Database Name] = DB_NAME(database_id),
[Size in MB] = CONCAT(CAST(((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2)),' 
MB') 
FROM   sys.master_files
WHERE  DB_NAME(database_id) NOT IN ('master', 'tempdev','tempdb')
GROUP BY      GROUPING SETS
(
(DB_NAME(database_id), Type_Desc),
(DB_NAME(database_id))
)
ORDER BY      DB_NAME(database_id), Type_Desc DESC
GO