DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10073
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @temptbl table 
(
[CSDBSizeGB] float,[WFDBSizeGB] float,[DM2DBSizeGB] float,[CVCloudDBSizeGB] float,[TempDBSizeGB] float,[TempDBLogSizeGB] float,[SQLServerVersion] nvarchar(max)
)
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; DECLARE @CSDBSize FLOAT = 0
 DECLARE @WFDBSize FLOAT = 0
DECLARE @DM2DBSize FLOAT = 0
DECLARE @CloudDBSize FLOAT = 0
DECLARE @TempDBSize FLOAT = 0
DECLARE @TempDBLogSize FLOAT = 0
SELECT @CSDBSize = ISNULL(cast(SUM(size)*8.0/1024.0/1024.0 as decimal(10,2)),0) --AS CurrentDBSizeInGB
      FROM sys.database_files
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = ''WFEngine'')
BEGIN
    SELECT @WFDBSize = ISNULL(CAST(SUM(fs.size)*8.0/1024.0/1024.0 as decimal(10,2)), 0) --AS CurrentWFEngineSizeGB
    FROM sys.master_files fs INNER JOIN sys.databases db ON fs.database_id = db.database_id AND db.name = ''WFEngine''
    GROUP BY db.database_id, db.name
END
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = ''DM2'')
BEGIN
    SELECT @DM2DBSize = ISNULL(CAST(SUM(fs.size)*8.0/1024.0/1024.0 as decimal(10,2)),0) --AS CurrentDM2SizeGB
    FROM sys.master_files fs INNER JOIN sys.databases db ON fs.database_id = db.database_id AND db.name = ''DM2''
    GROUP BY db.database_id, db.name
END
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = ''CVCloud'')
BEGIN
    SELECT @CloudDBSize = ISNULL(CAST(SUM(fs.size)*8.0/1024.0/1024.0 as decimal(10,2)),0) --AS CurrentCVCloudSizeGB
    FROM sys.master_files fs INNER JOIN sys.databases db ON fs.database_id = db.database_id AND db.name = ''CVCloud''
    GROUP BY db.database_id, db.name
END
select @TempDBSize = ISNULL(cast(sum(size)*8.0/1024.0/1024.0 as decimal(10,2)), 0) 
		from Tempdb.sys.database_files
SELECT @TempDBLogSize = ISNULL(cast(SUM(size)*8.0/1024.0/1024.0 as decimal(10,2)), 0)--AS TempLogSizeInGB 
      FROM sys.master_files
      WHERE name IN ( ''templog'')
SELECT 
	@CSDBSize AS ''CSDBSizeGB'',
	@WFDBSize AS ''WFDBSizeGB'',
	@DM2DBSize AS ''DM2DBSizeGB'',
	@CloudDBSize AS ''CVCloudDBSizeGB'',
	@TempDBSize AS ''TempDBSizeGB'',
	@TempDBLogSize AS ''TempDBLogSizeGB'',
	@@VERSION AS ''SQLServerVersion''
 ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [CSDBSizeGB] AS '@CSDBSizeGB',[WFDBSizeGB] AS '@WFDBSizeGB',[DM2DBSizeGB] AS '@DM2DBSizeGB',[CVCloudDBSizeGB] AS '@CVCloudDBSizeGB',[TempDBSizeGB] AS '@TempDBSizeGB',[TempDBLogSizeGB] AS '@TempDBLogSizeGB',[SQLServerVersion] AS '@SQLServerVersion' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10073'), type
)
  FOR XML PATH ('SurveyResults') 
)

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
SET @outputXML = (SELECT @queryId AS '@QueryId', @EndTime AS '@LogDate', (@EndTime - @LogDate) AS '@QueryRunningTime', @surveyXML FOR XML PATH('Rpt_CSSXMLDATA'))