更新与SELECT变量- TSQL

Question

我有一个表，其中包含特定服务器上所有数据库的名称。我希望能够用相应的DB大小更新DBSizeMB列。

到目前为止我的代码是:

DECLARE @DatabaseName VARCHAR(100)
UPDATE master.dbo.mytableName
    SET DBsizeMB = (SELECT total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
                      FROM master.sys.master_files)
    WHERE DBSizeMB = NULL
          AND DatabaseName = @DatabaseName

现在SELECT total size部分是对所有数据库的大小求和。

我如何设置它来更新每个单独的数据库?

Answer 1

Correlate the sub-query

UPDATE mt
SET    DBsizeMB = (SELECT total_size_mb = Cast(Sum(size) * 8. / 1024 AS DECIMAL(8, 2))
                   FROM   master.sys.master_files f
                          JOIN sys.databases d
                            ON f.database_id = d.database_id
                   WHERE  d.NAME = mt.DatabaseName)
FROM   master.dbo.mytableName mt
WHERE  DBSizeMB IS NULL 

JOIN version

WITH cte
     AS (SELECT total_size_mb = Cast(Sum(size) * 8. / 1024 AS DECIMAL(8, 2)),d.name
         FROM   master.sys.master_files f
                JOIN sys.databases d
                  ON f.database_id = d.database_id)
UPDATE mt
SET    DBsizeMB = c.total_size_mb
FROM   master.dbo.mytableName mt
       JOIN cte c
         ON c.NAME = mt.DatabaseName
WHERE  DBSizeMB IS NULL 

正如评论中提到的，不要在Master数据库中创建表。甚至在MSDN中也提到

不要在master中创建user对象。