Need output in single row

Question

Need output in single row

New persopn

SSCarpal Tunnel

Points: 4019
More actions
October 29, 2015 at 12:41 pm

#306128

Hi All,
I need out put in single row for below 2 queries using joins
SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB],
CPU_Count AS NumberofCPU
FROM [master].[sys].[dm_os_sys_memory]
CROSS JOIN sys.dm_os_sys_info
SELECT d.NAME,(SUM(mf.size) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf INNER JOIN sys.databases d
ON d.database_id = mf.database_id GROUP BY d.NAME
Order by (SUM(mf.size) * 8 / 1024) DESC

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 1

New persopn (10/29/2015)
Hi All,
I need out put in single row for below 2 queries using joins
SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB],
CPU_Count AS NumberofCPU
FROM [master].[sys].[dm_os_sys_memory]
CROSS JOIN sys.dm_os_sys_info
SELECT d.NAME,(SUM(mf.size) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf INNER JOIN sys.databases d
ON d.database_id = mf.database_id GROUP BY d.NAME
Order by (SUM(mf.size) * 8 / 1024) DESC

Quick suggestion

😎

SELECT

[total_physical_memory_kb] / POWER(2,10) AS [Total_Physical_Memory_In_MB]

,CPU_Count AS NumberofCPU

,X.DBSTRING

FROM [master].[sys].[dm_os_sys_memory]

CROSS JOIN sys.dm_os_sys_info

CROSS APPLY

(

SELECT

STUFF((SELECT

N', ' + d.NAME + N' (' +

CONVERT(NVARCHAR(20),(SUM(mf.size) * 8) / POWER(2,20),0)

+ N' Gb)'

FROM sys.master_files mf INNER JOIN sys.databases d

ON d.database_id = mf.database_id GROUP BY d.NAME

Order by SUM(mf.size) DESC

FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)'),1,2,'')

) AS X(DBSTRING)

RPSQL Hall of Fame Points: 3229 More actions · Answer 2

;WITH QUERY1 ( Total_Physical_Memory_In_MB,NumberofCPU )

AS (SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB],

CPU_Count AS NumberofCPU

FROM [master].[sys].[dm_os_sys_memory]

CROSS JOIN sys.dm_os_sys_info

),

QUERY2 ( DBNAME,Size_GBs )

AS ( SELECT d.NAME as DBNAME,(SUM(mf.size) * 8 / 1024) / 1024 AS Size_GBs

FROM sys.master_files mf INNER JOIN sys.databases d

ON d.database_id = mf.database_id GROUP BY d.NAME

)

SELECT DBNAME,Size_GBs,Total_Physical_Memory_In_MB,NumberofCPU

FROM Query1 , Query2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

RPSql (10/29/2015)
;WITH QUERY1 ( Total_Physical_Memory_In_MB,NumberofCPU )
AS (SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB],
CPU_Count AS NumberofCPU
FROM [master].[sys].[dm_os_sys_memory]
CROSS JOIN sys.dm_os_sys_info
),
QUERY2 ( DBNAME,Size_GBs )
AS ( SELECT d.NAME as DBNAME,(SUM(mf.size) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf INNER JOIN sys.databases d
ON d.database_id = mf.database_id GROUP BY d.NAME
)
SELECT DBNAME,Size_GBs,Total_Physical_Memory_In_MB,NumberofCPU
FROM Query1 , Query2

No need to use CTEs when you're working with single row views.

SELECT d.NAME,

(SUM(mf.size) * 8 / 1024) / 1024 AS Size_GBs,

[total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB],

CPU_Count AS NumberofCPU

FROM [master].[sys].[dm_os_sys_memory]

CROSS JOIN sys.dm_os_sys_info

CROSS JOIN sys.master_files mf

INNER JOIN sys.databases d ON d.database_id = mf.database_id

GROUP BY d.NAME,

[total_physical_memory_kb],

CPU_Count

ORDER BY (SUM(mf.size) * 8 / 1024) DESC;

By the way, you have the semicolon in the wrong side of the query. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

New persopn SSCarpal Tunnel Points: 4019 More actions · Answer 4

Thanks for your help..

Once final thing i want to add below query to above please suggest

SELECT servicename, service_account

FROM sys.dm_server_services