SQL Query to get output in single row

Question

How do write a query to retrieve the following data from SQL Server to be returned in a single row:
    • Server name
    • Minimum server memory
    • Maximum server memory
    • State of remote admin connection (DAC). If value is “1”, then output must be “Enabled”; otherwise, “Disabled”.
    • The number of SQL principals that are members of the [sysadmin] fixed server role.

Answer 1

For question 1-4 see SERVERPROPERTY (Transact-SQL)

Answer 2

Hi @SM

Server name:

select @@SERVERNAME 

Minimum server memory and Maximum server memory:

SELECT [name], [value], [value_in_use]
FROM sys.configurations
WHERE [name] = 'max server memory (MB)' OR [name] = 'min server memory (MB)';

The number of SQL principals that are members of the [sysadmin] fixed server role:

SELECT	roles.principal_id							AS RolePrincipalID
	,	roles.name									AS RolePrincipalName
	,	server_role_members.member_principal_id		AS MemberPrincipalID
	,	members.name								AS MemberPrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals AS members 
    ON server_role_members.member_principal_id = members.principal_id;

Best regards,

Percy Tang

Answer 3

If I understood correctly you need output as 1 Row of data for a server, so taking what Percy Tang Provided little further you can get this data from following query

select @@SERVERNAME , (SELECT [value_in_use] FROM sys.configurations WHERE [name] = 'min server memory (MB)') AS MinServerMemory,
(SELECT [value_in_use] FROM sys.configurations WHERE [name] = 'max server memory (MB)') AS MaxServerMemory,
(SELECT case when value_in_use=1 THEN 'Enable' ELSE 'Disable' END FROM sys.configurations where name like 'remote admin connections') AS DACStatus,
(SELECT	count(1) FROM sys.server_role_members AS srm INNER JOIN sys.server_principals AS p ON srm.role_principal_id = p.principal_id INNER JOIN sys.server_principals AS u     ON srm.member_principal_id = u.principal_id WHERE p.principal_id=3) AS TotalSysAdmins