Been trying to add a variable to my code (below) to: sum up all size values returned by the SELECT into said variable and then place that
value at the bottom of the [size in MB] column. Must be dense as I haven;t been able to figure that out yet. Need to resolve this before I tackle the
next piece to have it programmatically save as a CSV file.
Any help appreciated.
Thanks
-------------------code ------------------------------
DECLARE @TOTALSIZE FLOAT(50)
DECLARE @Size_one FLOAT(50)
SELECT [Database Name] = DB_NAME(database_id),
[Size in MB] = CAST(((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2)), -- DB size in MB
-- Add a column with who the customer
CASE
WHEN DB_NAME(database_id) = 'xxxx' THEN ' xxxx'
WHEN (DB_NAME(database_id) IN ('ARxxx' , 'xxxx' , 'Smaxxxxx')) THEN ' xxxx'
WHEN DB_NAME(database_id) = 'xxxx' THEN ' XXXX'
WHEN DB_NAME(database_id) = 'xxxx_xxxx' or SUBSTRING(DB_NAME(database_id),1,7) = 'Insight' THEN ' xxxx'
WHEN SUBSTRING(DB_NAME(database_id),1,3) = 'xxxx' or SUBSTRING(DB_NAME(database_id),1,3) = 'WSS' or SUBSTRING(DB_NAME(database_id),1,7) = 'BackupL' or SUBSTRING(DB_NAME(database_id),1,5) = 'xxxxod' THEN ' xxxx'
WHEN SUBSTRING(DB_NAME(database_id),1,3) = 'xxxx' THEN ' xxxx'
WHEN SUBSTRING(DB_NAME(database_id),1,6) = 'xxxx' or SUBSTRING(DB_NAME(database_id),1,4) = 'xxxx' THEN ' xxxx'
WHEN SUBSTRING(DB_NAME(database_id),1,4) = 'OPDR' THEN ' xxxx'
WHEN SUBSTRING(DB_NAME(database_id),1,4) = 'xxxx' THEN ' xxxx'
WHEN SUBSTRING(DB_NAME(database_id),1,4) = 'Repo' THEN ' SSRS'
WHEN SUBSTRING(DB_NAME(database_id),1,5) = 'xxxx' or SUBSTRING(DB_NAME(database_id),1,4) = 'Warr' THEN ' XXXXX'
WHEN SUBSTRING(DB_NAME(database_id),1,3) = 'For' THEN ' TEAM_SQL'
ELSE 'Unknown'
END as Customer,
-- Get the server Name so we can assiciate to which xxxx, xxxxod or xxxx the instance is in
CASE
WHEN SUBSTRING(@@SERVERNAME,3,3) = 'uxx' THEN ' xxxx'
WHEN SUBSTRING(@@SERVERNAME,3,3) = 'uxr' THEN ' xxxx'
WHEN SUBSTRING(@@SERVERNAME,3,3) = 'xxxx' or SUBSTRING(@@SERVERNAME,5,3) = 'mei' THEN ' xxxxod'
ELSE '????'
END as ' Area'
FROM sys.master_files
WHERE DB_NAME(database_id) NOT IN ('master', 'tempdev','tempdb', 'model','msdb')
GROUP BY GROUPING SETS
(
(DB_NAME(database_id), Type_Desc),
(DB_NAME(database_id))
)
ORDER BY DB_NAME(database_id), Type_Desc DESC
GO
-------------------------------- OUTPUT of Query --------------------------------
Server Name Database Name Size in MB Customer Area
xxxxxx001 XXXXXXXXX 1001.88 Big_Customer Production
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
TOTAL MB Disk space used is: 29,443,247.13
TOTAL GB Disk space used is: 29,443.25
TOTAL TB Disk space used is: 29.44