AznDevil92 AznDevil92 - 1 month ago 7
SQL Question

UPDATE with SELECT Variable - TSQL

I have table that has all the names of the databases that are on a particular server. I want to be able to update the DBSizeMB column with the pertaining DB size.

So far my code is:

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


Right now the SELECT total size part is summing up all of the databases' sizes.

How can I set it to update each individual database?

Answer

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 

Also as mentioned in comments, do not create tables in Master database. Even in MSDN it is mentioned that

Do not create user objects in master.