I’m trying to fetch information about a database and I have the following query that prints most of it out:
SELECT Serverproperty('ServerName') AS [Server], (SELECT Server_type = CASE WHEN virtual_machine_type = 1 THEN 'Virtual' ELSE 'Physical' END FROM sys.dm_os_sys_info) AS [Virtual/Physical], (SELECT ( cpu_count / hyperthread_ratio ) FROM sys.dm_os_sys_info) AS [CPU(s)], (SELECT CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count WHEN ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) ) = 0 THEN 1 END FROM sys.dm_os_sys_info) AS [Core(s)], Substring ((SELECT @@VERSION), 1, Charindex('-', (SELECT @@VERSION)) - 1) AS [Product Name], Serverproperty('ProductVersion') AS [Product Version], Serverproperty('ProductLevel') AS [Product Level], Serverproperty('Edition') AS [Edition]
Result:
This is where my issue comes in. I found a script that prints out all your instances on the server, too. Like this:
DECLARE @GetInstances TABLE ( value NVARCHAR(100), instancenames NVARCHAR(100), data NVARCHAR(100) ) INSERT INTO @GetInstances EXECUTE Xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'SOFTWAREMicrosoftMicrosoft SQL Server', @value_name = 'InstalledInstances' SELECT instancenames FROM @GetInstances
My issue is that I cannot seem to forge the first query with the second by including it in the SELECT-statement of the first script. I get the error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
How can I fix this? I would like the result to be printed out in the form of the image below, is that possible?