How To Find Server details using SQL Query

Today I got a task to collect hardware details of all servers I m supporting, Oh hell we have 500 + servers its not possible to go on each server and collect manually even though we have a big team here.

Guess what junior DBA's already start doing this manually, But that's not the right way we are DBA's

So as usual started Google but no luck and I decided to right script by my own and here's the result.

This script is good for SQL server 2008 and above (if we comment bit of code we can use same in sql 2005 as well)

Result set will be:

Server name	NUMBER_OF_PROCESSORS	System Manufacturer	Processor Type	Total RAM in GB	IP Address	System Model

Document on same is available @ :http://gallery.technet.microsoft.com/How-To-Find-System-1231eaee
/********************START OF SCRIPT***************************************/
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


----------Summarizing 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

/**********************END OF SCRIPT*************************************/


Note : SQL query to find windows version using registry