How to Find System Server details using SQL

DECLARE @test varchar(20) ,

            @key varchar(100),

            @NUMBER_OF_PROCESSORS  varchar(20) ,

            @PROCESSOR_IDENTIFIER  varchar(100) ,

            @SystemManufacturer  varchar(20) ,

            @ProcessorNameString varchar (100),

            @connection varchar (50),

            @SystemProductName varchar (100)

select

            @connection = convert (varchar (50),CONNECTIONPROPERTY('local_net_address'),1)

set @key = 'System\CurrentControlSet\Control\Session Manager\Environment'

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

                                    @key=@key,@value_name='NUMBER_OF_PROCESSORS',

                                    @value=@NUMBER_OF_PROCESSORS

                  OUTPUT

set @key = 'HARDWARE\DESCRIPTION\system\BIOS'

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

                                    @key=@key,@value_name='SystemProductName',

                                    @value=@SystemProductName

                  OUTPUT

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

                                    @key=@key,@value_name='SystemManufacturer',

                                    @value=@SystemManufacturer

                  OUTPUT

set @key = 'HARDWARE\DESCRIPTION\system\CentralProcessor\0'

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

                                    @key=@key,@value_name='ProcessorNameString',

                                    @value=@ProcessorNameString

                  OUTPUT

set @key = 'SOFTWARE\Microsoft\Windows\CurrentVersion\OEMInformation'

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

                                    @key=@key,@value_name='ProcessorNameString',

                                    @value=@ProcessorNameString

                  OUTPUT

----------Generating Output------------------

SELECT      @@ServerName as [Server name],

            convert(varchar(10),@NUMBER_OF_PROCESSORS)as [NUMBER_OF_PROCESSORS],

            @SystemManufacturer as SystemManufacturer,

            @ProcessorNameString as [Processor Type],

            [total_physical_memory_kb] / (1024*1024) AS [Total RAM in GB],

            @connection AS [IP Address],

            @SystemProductName as [System Model]

from

            sys.dm_os_sys_memory

will get bellow listed column values

Source: Internet