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

• Eirikur Eiriksson

  SSC Guru

  Points: 182942

  More actions

  October 29, 2015 at 1:24 pm

  #1836497

  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

  October 29, 2015 at 1:28 pm

  #1836498

  ;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

  October 29, 2015 at 1:45 pm

  #1836502

  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

  October 29, 2015 at 1:51 pm

  #1836504

  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