UPDATE with SELECT Variable - TSQL

Question

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?

Your code isn't going to work quite like this at all. sys.master_files is ALL the files for every database. And definitely do NOT create your own tables in master. This is a very bad idea. — Sean Lange
– Sean Lange, Commented Oct 28, 2016 at 15:26
Okay, but it is on a development server and it was approved by senior DBA's to be stored on the system db — AznDevil92
– AznDevil92, Commented Oct 28, 2016 at 15:27
Not that hard to create a Utilities database for stuff like this, for a start later migrating master elsewhere is not (really) possible. — Alex K.
– Alex K., Commented Oct 28, 2016 at 15:28
As I understand it, the only thing that should go in master is stored procedures. And even then, we're talking about global utilities like sp_Who2 and sp_Blitz, not ones that are used by applications. — Jonathan Allen
– Jonathan Allen, Commented Oct 28, 2016 at 16:02

AznDevil92 · Accepted Answer · 2016-10-28 15:56:51Z

2

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.

edited Oct 28, 2016 at 15:56

AznDevil92

5543 gold badges15 silver badges42 bronze badges

answered Oct 28, 2016 at 15:26

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AznDevil92 Over a year ago

I needed to change 'WHERE DBSizeMB IS NULL' instead of '= NULL'

Collectives™ on Stack Overflow

UPDATE with SELECT Variable - TSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related