Sometime back, I was involved in a project that consisted in collecting information for the servers we are supporting – creating an inventory of servers. Logging into each server and collecting information is a bit tedious when you have hundreds of servers in your environment. I have spent sometime creating a script that does this work for me; however, I still need to connect to the server – but using SSMS/Query Window – and execute the script. And then the script will provide me the details I need. Please remember this script is enabling xp_cmdshell and disabling it back, before you run this script if this is acceptable in your environment.
This inventory should have details like below for cluster and stand alone instances:
Server Name
OS Name
OS Edition
OS Patch Level
SQL Server IP
Is Clustered
Node1_Name
Node1_IP
Node2_Name
Node2_IP
SQL Server Edition
SQL Server Patch Level
Server Time Zone
SQL Server Version
SQL Server Platform
Processor Core
Physical Memory
Service Account Name
Domain
Looks good ?
IP address portion using : https://www.sqlservercentral.com/Forums/Topic150196-8-1.aspx REMOVE sp_configure parameters if you are executing this script on SQL Server 2000 Created By : Hemantgiri S. Goswami | https://www.sql-server-citation.com Date : 24th March 2013 Version : 1.0 Tested ON: Windows Server >> 2003, 2008, 2008 R2 SQL Server >> 2000, 2005, 2008, 2008 R2, 2012 */ sp_configure ‘show advanced options’,1 RECONFIGURE WITH OVERRIDE GO sp_configure ‘xp_cmdshell’,1 RECONFIGURE WITH OVERRIDE GO DECLARE @TimeZone NVARCHAR(100) ,@ProductVersion SYSNAME ,@PlatForm SYSNAME ,@Windows_Version SYSNAME ,@Processors SYSNAME ,@PhysicalMemory SYSNAME ,@ServiceAccount SYSNAME ,@IPAddress SYSNAME ,@DOMAIN SYSNAME ,@MachineType SYSNAME ,@SQLServerIP VARCHAR(255) ,@CMD VARCHAR(100) ,@Node1 VARCHAR(100) ,@Node2 VARCHAR(100) ,@Node1IP VARCHAR(100) ,@Node2IP VARCHAR(100) ,@OSEdition VARCHAR(100) ,@OSVersion VARCHAR(100) ,@OSName VARCHAR(100) ,@OSPatchLevel VARCHAR(100) CREATE TABLE #TempTable ( [Index] VARCHAR(2000), [Name] VARCHAR(2000), [Internal_Value] VARCHAR(2000), [Character_Value] VARCHAR(2000) ) ; INSERT INTO #TempTable EXEC xp_msver; — Replace @Value_Name to N’TimeZoneKeyName’ when running on Windows 2008 EXEC master.dbo.xp_regread @rootkey = N’HKEY_LOCAL_MACHINE’, @key = N’SYSTEM\CurrentControlSet\Control\TimeZoneInformation’, @value_name = N’StandardName’, @value = @TimeZone output EXEC master.dbo.xp_regread @rootkey = N’HKEY_LOCAL_MACHINE’, @key = N’SYSTEM\CurrentControlSet\Services\MSSQLServer’, @value_name = N’ObjectName’, @value = @ServiceAccount output EXEC master.dbo.xp_regread @rootkey = N’HKEY_LOCAL_MACHINE’, @key = N’SYSTEM\CurrentControlSet\Control\ProductOptions’, @value_name = N’ProductType’, @value = @MachineType output EXEC master.dbo.xp_regread @rootkey = N’HKEY_LOCAL_MACHINE’, @key = N’SYSTEM\CurrentControlSet\Services\Tcpip\Parameters’, @value_name = N’Domain’, @value = @DOMAIN output EXEC master.dbo.xp_regread @rootkey = N’HKEY_LOCAL_MACHINE’, @key = N’CLUSTER\NODES\1′, @value_name = N’NodeName’, @value = @Node1 output EXEC master.dbo.xp_regread @rootkey = N’HKEY_LOCAL_MACHINE’, @key = N’CLUSTER\NODES\2′, @value_name = N’NodeName’, @value = @Node2 output EXEC master.dbo.xp_regread @rootkey = N’HKEY_LOCAL_MACHINE’, @key = N’SOFTWARE\Microsoft\Windows NT\CurrentVersion’, @value_name = N’ProductName’, @value = @OSName output create table #OSEdition (VALUe varchar(255),OSEdition varchar(255), data varchar(100)) insert into #OSEdition EXEC master.dbo.xp_regread @rootkey = N’HKEY_LOCAL_MACHINE’, @key = N’SYSTEM\CurrentControlSet\Control\ProductOptions’, @value_name = N’ProductSuite’ SET @OSEdition = (SELECT TOP 1 OSedition FROM #OsEdition) EXEC master.dbo.xp_regread @rootkey = N’HKEY_LOCAL_MACHINE’, @key = N’SOFTWARE\Microsoft\Windows NT\CurrentVersion’, @value_name = N’CSDVersion’, @value = @OSPatchLevel output set @cmd = ‘ping ‘ + @Node1 create table #Node1IP (grabfield varchar(255)) insert into #Node1IP exec master.dbo.xp_cmdshell @cmd set @cmd = ‘ping ‘ + @Node2 create table #Node2IP (grabfield varchar(255)) insert into #Node2IP exec master.dbo.xp_cmdshell @cmd set @cmd = ‘ping ‘ + @@servername create table #SQLServerIP (grabfield varchar(255)) insert into #SQLServerIP exec master.dbo.xp_cmdshell @cmd SET @SQLServerIP = ( SELECT substring(grabfield, charindex(‘[‘,grabfield)+1, charindex(‘]’,grabfield)-charindex(‘[‘,grabfield)-1) from #SQLServerIP where left(grabfield,7) = ‘Pinging’ ) SET @Node1IP = ( SELECT substring(grabfield, charindex(‘[‘,grabfield)+1, charindex(‘]’,grabfield)-charindex(‘[‘,grabfield)-1) from #Node1IP where left(grabfield,7) = ‘Pinging’ ) SET @Node2IP = ( SELECT substring(grabfield, charindex(‘[‘,grabfield)+1, charindex(‘]’,grabfield)-charindex(‘[‘,grabfield)-1) from #Node2IP where left(grabfield,7) = ‘Pinging’ ) SET @ProductVersion = (SELECT Character_Value from #TempTable where [INDEX]=2) SET @Platform = (SELECT Character_Value from #TempTable where [INDEX]=4) SET @Windows_Version= (SELECT Character_Value from #TempTable where [INDEX]=15) SET @Processors = (SELECT Character_Value from #TempTable where [INDEX]=16) SET @PhysicalMemory = (SELECT Character_Value from #TempTable where [INDEX]=19) SELECT ServerName = @@SERVERNAME ,OSName = @OSName ,OSEdition = @OSEdition ,OSPatchLevel = @OSPatchLevel ,SQLServerIP = @SQLServerIP ,IsClustered = SERVERPROPERTY(‘IsClustered’) ,Node1_Name = @Node1 ,Node1_IP = @Node1IP ,Node2_Name = @Node2 ,Node2_IP = @Node2IP ,SQLServerEdition = SERVERPROPERTY(‘Edition’) ,SQLServerLevel = SERVERPROPERTY(‘ProductLevel’) ,ServerTimeZone = @TimeZone ,SQLServerVersion = @ProductVersion ,SQLServerPlatform = @PlatForm ,ProcessorCore = @Processors ,PhysicalMemory = @PhysicalMemory ,ServiceAccountName = @ServiceAccount ,WKS_Server = @MachineType ,Domain = @DOMAIN GO DROP TABLE #Node1IP DROP TABLE #NODE2IP DROP TABLE #SQLServerIP DROP TABLE #TempTable DROP TABLE #OSEdition GO sp_configure ‘xp_cmdshell’,0 RECONFIGURE WITH OVERRIDE GO sp_configure ‘show advanced options’,0 RECONFIGURE WITH OVERRIDE GO
Ready to optimize your SQL Server Database for the future?