T-SQL Query to get the data for inventory

Hi All,

The following query can be used to get the data needed for an Inventory. This includes the server name, version, patching info etc., We can create registered server and add all the servers available with us and run the query in centralized manner. in one shot we can gather all the required details needed for an inventory.

Code:

Set NoCount On
Declare @CurrID int,@ExistValue int, @MaxID int, @SQL nvarchar(1000)
Declare @TCPPorts Table (PortType nvarchar(180), Port int)
Declare @SQLInstances Table (InstanceID int identity(1, 1) not null primary key,
                                          InstName nvarchar(180),
                                          Folder nvarchar(50),
                                          StaticPort int null,
                                          DynamicPort int null,
                                          Platform int null);
Declare @Plat Table (Id int,Name varchar(180),InternalValue varchar(50), Charactervalue varchar (50))
Declare @Platform varchar(100)
Insert into @Plat exec xp_msver platform
select @Platform = (select 1 from @plat where charactervalue like '%86%')
If @Platform is NULL
Begin
Insert Into @SQLInstances (InstName, Folder)
Exec xp_regenumvalues N'HKEY_LOCAL_MACHINE',
                             N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';
Update @SQLInstances set Platform=64
End
else
Begin
Insert Into @SQLInstances (InstName, Folder)
Exec xp_regenumvalues N'HKEY_LOCAL_MACHINE',
                             N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';
Update @SQLInstances Set Platform=32
End 
Declare @Keyexist Table (Keyexist int)
Insert into @Keyexist
Exec xp_regread'HKEY_LOCAL_MACHINE',
                              N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';
select @ExistValue= Keyexist from @Keyexist
If @ExistValue=1
Insert Into @SQLInstances (InstName, Folder)
Exec xp_regenumvalues N'HKEY_LOCAL_MACHINE',
                              N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';
Update @SQLInstances Set Platform =32 where Platform is NULL
Select @MaxID = MAX(InstanceID), @CurrID = 1
From @SQLInstances
While @CurrID <= @MaxID
  Begin
      Delete From @TCPPorts
      Select @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
                              N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
                              N''TCPDynamicPorts'''
      From @SQLInstances
      Where InstanceID = @CurrID
      Insert Into @TCPPorts
      Exec sp_executesql @SQL
      Select @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
                             N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
                              N''TCPPort'''
      From @SQLInstances
      Where InstanceID = @CurrID
      Insert Into @TCPPorts
      Exec sp_executesql @SQL
      Select @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
                              N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
                              N''TCPDynamicPorts'''
      From @SQLInstances
      Where InstanceID = @CurrID
      Insert Into @TCPPorts
      Exec sp_executesql @SQL
      Select @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',                              N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'', N''TCPPort''' From @SQLInstances
Where InstanceID = @CurrID
      Insert Into @TCPPorts
      Exec sp_executesql @SQL
      Update SI
      Set StaticPort = P.Port,
            DynamicPort = DP.Port
      From @SQLInstances SI
      Inner Join @TCPPorts DP On DP.PortType = 'TCPDynamicPorts'
      Inner Join @TCPPorts P On P.PortType = 'TCPPort'
      Where InstanceID = @CurrID;
      Set @CurrID = @CurrID + 1
  End
Select serverproperty('ComputerNamePhysicalNetBIOS') as ServerName, InstName,(select COUNT(*) from sys.dm_os_schedulers where status = 'VISIBLE ONLINE') as logical_processors , (select (cpu_count / hyperthread_ratio) 'physical_cpus' from sys.dm_os_sys_info) as phiysical_processors, (select cast(round(cast(physical_memory_kb AS Numeric(15,4))/1024,2) as decimal(10,2)) from sys.dm_os_sys_info )as totalsize,StaticPort, DynamicPort,Platform,case serverproperty('IsClustered')
when 0 then 'No'
when 1 then 'Yes'
end as IsClust,
case serverproperty('Ishadrenabled')
when 0 then 'Disabled'
when 1 then 'Enabled'
else 'Not Applicable'
end as AlwaysON,
SERVERPROPERTY('productversion') as SQLVersion, SERVERPROPERTY('ProductLevel') as ServicePacklevel
From @SQLInstances
Set NoCount Off

Thanks for the Anonymous commenter, I have added his/her suggestion in the script.