CODEWITHSUNDEEP
sqlsql-servert-sql
AznDevil92
AznDevil92

Reputation: 554

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?

Upvotes: 0

Views: 102

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

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.

Upvotes: 2

Related Questions