Recently one of our clients requested information about their SQL Server infrastructure which we manage. In order to complete this task, I’ve written the following Transact-SQL script to retrieve information about SQL Server infrastructure.
This script is compatible with SQL Server 2005 and above:
SET NOCOUNT ON; DECLARE @SQLServerStartupMode [int] ,@SQLAgentStartupMode [int] ,@LoadID [int] ,@Position [int] ,@LoginMode [int] ,@SQLServerAuditLevel [int] ,@SQLServerStartupType [char](12) ,@SQLAgentStartupType [char](12) ,@SQLServerServiceAccount [varchar](64) ,@SQLAgentServiceAccount [varchar](64) ,@SQLServerRegistryKeyPath [varchar](256) ,@SQLAgentRegistryKeyPath [varchar](256) ,@InstanceName [nvarchar](128) ,@FullInstanceName [nvarchar](128) ,@SystemInstanceName [nvarchar](128) ,@ErrorLogDirectory [nvarchar](128) ,@Domain [nvarchar](64) ,@IPLine [nvarchar](256) ,@IpAddress [nvarchar](16) ,@ActiveNode [nvarchar](128) ,@AuthenticationMode [varchar](64) ,@PortNumber [varchar](8) ,@PageFile [varchar](124) ,@ClusterNodes [nvarchar](32) ,@BinariesPath [nvarchar](128) ,@RegistryKeyPath [nvarchar](256) ,@RegistryPath1 [nvarchar](256) ,@RegistryPath2 [nvarchar](256) ,@RegistryPath3 [nvarchar](256) ,@SQLServerInstallationLocation [nvarchar](512) IF OBJECT_ID('[Tempdb].[dbo].[#_IPCONFIG_OUTPUT]') IS NOT NULL DROP TABLE [dbo].[#_IPCONFIG_OUTPUT] IF OBJECT_ID('[Tempdb].[dbo].[#_PAGE_FILE_DETAILS]') IS NOT NULL DROP TABLE [dbo].[#_PAGE_FILE_DETAILS] IF OBJECT_ID('[Tempdb].[dbo].[#_XPMSVER]') IS NOT NULL DROP TABLE [dbo].[#_XPMSVER] IF EXISTS (SELECT * FROM [tempdb].[sys].[objects] WHERE [name] = '##_SERVER_CONFIG_INFO' AND [type] IN (N'U')) DROP TABLE [dbo].[##_SERVER_CONFIG_INFO] CREATE TABLE [dbo].[#_PAGE_FILE_DETAILS] ([data] [varchar](500)) CREATE TABLE [dbo].[#_IPCONFIG_OUTPUT] ([IPConfigCommandOutput] [nvarchar](256)) CREATE TABLE [dbo].[#_XPMSVER]([IDX] [int] NULL ,[C_NAME] [varchar](100) NULL ,[INT_VALUE] [float] NULL ,[C_VALUE] [varchar](128) NULL ) ON [PRIMARY] CREATE TABLE [dbo].[##_SERVER_CONFIG_INFO]( [Domain] [nvarchar](64) NULL, [SQLServerName] [varchar](64) NULL, [InstanceName] [nvarchar](128) NULL, [ComputerNamePhysicalNetBIOS] [nvarchar](128) NULL, [IsClustered] [varchar](13) NULL, [ClusterNodes] [nvarchar](32) NULL, [ActiveNode] [nvarchar](128) NULL, [HostIPAddress] [nvarchar](16) NULL, [PortNumber] [varchar](8) NULL, [IsIntegratedSecurityOnly] [varchar](64) NULL, [AuditLevel] [varchar](38) NOT NULL, [ProductVersion] [varchar](100) NULL, [ProductLevel] [varchar](100) NULL, [ResourceVersion] [varchar](100) NULL, [ResourceLastUpdateDateTime] [varchar](100) NOT NULL, [EngineEdition] [varchar](64) NULL, [BuildClrVersion] [varchar](100) NOT NULL, [Collation] [varchar](100) NULL, [CollationID] [varchar](100) NULL, [ComparisonStyle] [varchar](100) NULL, [IsFullTextInstalled] [varchar](26) NULL, [SQLCharset] [varchar](100) NOT NULL, [SQLCharsetName] [varchar](100) NOT NULL, [SQLSortOrderID] [varchar](100) NOT NULL, [SQLSortOrderName] [varchar](100) NOT NULL, [Platform] [varchar](128) NULL, [FileDescription] [varchar](128) NULL, [WindowsVersion] [varchar](128) NULL, [ProcessorCount] [float] NULL, [ProcessorType] [varchar](128) NULL, [PhysicalMemory] [float] NULL, [ServerPageFile] [varchar](124) NULL, [SQLInstallationLocation] [nvarchar](512) NULL, [BinariesPath] [nvarchar](128) NULL, [ErrorLogsLocation] [nvarchar](128) NULL, [MSSQLServerServiceStartupUser] [varchar](64) NULL, [MSSQLAgentServiceStartupUser] [varchar](64) NULL, [MSSQLServerServiceStartupType] [char](12) NULL, [MSSQLAgentServiceStartupType] [char](12) NULL, [InstanceLastStartDate] [datetime] NULL, [LoadID] [int]) ON [PRIMARY] ------ Finding SQL Server and Agent Service Account Information ------ IF SERVERPROPERTY('InstanceName') IS NULL -- Default Instance BEGIN --default instance SET @SQLServerRegistryKeyPath='SYSTEM\CurrentControlSET\SERVICES\MSSQLSERVER' SET @SQLAgentRegistryKeyPath='SYSTEM\CurrentControlSET\SERVICES\SQLSERVERAGENT' END ELSE BEGIN --Named Instance SET @SQLServerRegistryKeyPath = 'SYSTEM\CurrentControlSET\SERVICES\MSSQL$' + CAST (SERVERPROPERTY('InstanceName') AS [sysname]) SET @SQLAgentRegistryKeyPath = 'SYSTEM\CurrentControlSET\SERVICES\SQLAgent$' + CAST (SERVERPROPERTY('InstanceName') AS [sysname]) END EXEC [master]..[xp_regread] 'HKEY_LOCAL_MACHINE' ,@SQLServerRegistryKeyPath ,@value_name = 'Start' ,@value = @SQLServerStartupMode OUTPUT EXEC [master]..[xp_regread] 'HKEY_LOCAL_MACHINE' ,@SQLAgentRegistryKeyPath ,@value_name = 'Start' ,@value = @SQLAgentStartupMode OUTPUT SET @SQLServerStartupType = (SELECT 'Start Up Mode' = CASE WHEN @SQLServerStartupMode = 2 THEN 'Automatic' WHEN @SQLServerStartupMode = 3 THEN 'Manual' WHEN @SQLServerStartupMode = 4 THEN 'Disabled' END) SET @SQLAgentStartupType = (SELECT 'Start Up Mode' = CASE WHEN @SQLAgentStartupMode = 2 THEN 'Automatic' WHEN @SQLAgentStartupMode = 3 THEN 'Manual' WHEN @SQLAgentStartupMode = 4 THEN 'Disabled' END) EXEC [master]..[xp_regread] 'HKEY_LOCAL_MACHINE' ,@SQLServerRegistryKeyPath ,@value_name = 'ObjectName' ,@value = @SQLServerServiceAccount OUTPUT EXEC [master]..[xp_regread] 'HKEY_LOCAL_MACHINE' ,@SQLAgentRegistryKeyPath ,@value_name = 'ObjectName' ,@value = @SQLAgentServiceAccount OUTPUT ------ Reading registry keys for Binaries, Errorlogs location and Domain ------ SET @InstanceName = COALESCE (CONVERT([nvarchar](100) , SERVERPROPERTY('InstanceName')), 'MSSQLSERVER'); IF @InstanceName != 'MSSQLSERVER' BEGIN SET @InstanceName = @InstanceName END SET @FullInstanceName = COALESCE (CONVERT([nvarchar](100) , SERVERPROPERTY('InstanceName')), 'MSSQLSERVER'); IF @FullInstanceName != 'MSSQLSERVER' BEGIN SET @FullInstanceName = 'MSSQL$'+ @FullInstanceName END EXEC [master]..[xp_regread] N'HKEY_LOCAL_MACHINE' ,N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL' ,@InstanceName ,@SystemInstanceName OUTPUT; SET @RegistryKeyPath = N'SYSTEM\CurrentControlSET\Services\' + @FullInstanceName; SET @RegistryPath1 = N'Software\Microsoft\Microsoft SQL Server\' + @SystemInstanceName + '\MSSQLServer\Parameters'; SET @RegistryPath2 = N'Software\Microsoft\Microsoft SQL Server\' + @SystemInstanceName + '\MSSQLServer\supersocketnetlib\TCP\IP1'; SET @RegistryPath3 = N'SYSTEM\ControlSET001\Services\Tcpip\Parameters\'; IF @RegistryPath1 IS NULL BEGIN SET @InstanceName = COALESCE(CONVERT([nvarchar](100) ,SERVERPROPERTY('InstanceName')), 'MSSQLSERVER'); END EXEC [master]..[xp_regread] N'HKEY_LOCAL_MACHINE' ,N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL' ,@InstanceName ,@SystemInstanceName OUTPUT; EXEC [master]..[xp_regread] N'HKEY_LOCAL_MACHINE' ,@RegistryKeyPath ,@value_name = 'ImagePath' ,@value = @BinariesPath OUTPUT EXEC [master]..[xp_regread] N'HKEY_LOCAL_MACHINE' ,@RegistryPath1 ,@value_name = 'SQLArg1' ,@value = @ErrorLogDirectory OUTPUT EXEC [master]..[xp_regread] N'HKEY_LOCAL_MACHINE' ,@RegistryPath3 ,@value_name = 'Domain' ,@value = @Domain OUTPUT SELECT @ClusterNodes = COALESCE(@ClusterNodes+', ' ,'') + [Nodename] FROM [sys].[dm_os_cluster_nodes] IF @ClusterNodes IS NULL BEGIN SET @ClusterNodes = 'Not Clustered' END SET @InstanceName = CONVERT([varchar](25), SERVERPROPERTY('InstanceName')) EXEC [master]..[xp_instance_regread] N'HKEY_LOCAL_MACHINE' ,N'Software\Microsoft\MSSQLServer\MSSQLServer' ,N'AuditLevel' ,@SQLServerAuditLevel OUTPUT EXEC [master]..[xp_instance_regread] N'HKEY_LOCAL_MACHINE' ,N'SOFTWARE\Microsoft\MSSQLServer\Setup' ,N'SQLPath' ,@SQLServerInstallationLocation OUTPUT ------ Finding IP Address ------ INSERT #_IPCONFIG_OUTPUT EXEC [master]..[xp_cmdshell] 'ipconfig' IF LEFT (CAST(SERVERPROPERTY('ProductVersion')AS [sysname]), 5) = '10.50' BEGIN SELECT @IPLine = [IPConfigCommandOutput] FROM #_IPCONFIG_OUTPUT WHERE UPPER ([IPConfigCommandOutput]) LIKE '%IPv4 Address%' IF (ISNULL(@IPLine,'***') != '***') BEGIN SET @Position = CharIndex (':', @IPLine, 1); SET @IPAddress = RTRIM(LTRIM(SUBSTRING(@IPLine ,@Position + 1 ,LEN(@IPLine) - @Position))) END END ELSE BEGIN SELECT @IPLine = [IPConfigCommandOutput] FROM #_IPCONFIG_OUTPUT WHERE UPPER ([IPConfigCommandOutput]) LIKE '%IP Address%' IF (ISNULL(@IPLine,'***') != '***') BEGIN SET @Position = CharIndex (':', @IPLine, 1); SET @IPAddress = RTRIM(LTRIM(SUBSTRING(@IPLine ,@Position + 1,LEN(@IPLine) - @Position))) END END ------ Finding Port Information ------ IF @InstanceName IS NULL BEGIN SET @RegistryKeyPath = 'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\' END ELSE BEGIN SET @RegistryKeyPath = 'Software\Microsoft\Microsoft SQL Server\' + @InstanceName + '\MSSQLServer\SuperSocketNetLib\Tcp\' END EXEC [master]..[xp_regread] 'HKEY_LOCAL_MACHINE' ,@RegistryKeyPath ,@value_name = 'tcpPort' ,@value = @PortNumber OUTPUT -- Port Number ------ Finding Authentication Mode ------ EXEC [master]..[xp_instance_regread] N'HKEY_LOCAL_MACHINE' ,N'Software\Microsoft\MSSQLServer\MSSQLServer' ,@value_name = N'LoginMode' ,@value = @LoginMode OUTPUT SET @AuthenticationMode = (SELECT 'AuTHENtication Mode' = CASE WHEN @LoginMode = 1 THEN 'Windows Authentication' WHEN @LoginMode = 2 THEN 'Mixed Mode Authentication' END ) ------ Finding Active Node ------ EXEC [master]..[xp_regread] @rootkey = 'HKEY_LOCAL_MACHINE' ,@RegistryKeyPath = 'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName' ,@value_name = 'ComputerName' ,@value = @ActiveNode OUTPUT INSERT INTO [#_PAGE_FILE_DETAILS] EXEC [master]..[xp_cmdshell] 'wmic pagefile list /format:list' SELECT @PageFile = RTRIM(LTRIM([data])) FROM #_PAGE_FILE_DETAILS WHERE [data] LIKE 'AllocatedBaseSize%' INSERT INTO [#_XPMSVER] EXEC( 'master.dbo.xp_msver') SELECT UPPER(@Domain) AS [Domain] ,CONVERT([varchar](64),SERVERPROPERTY('ServerName')) AS [SQLServerName] ,@FullInstanceName AS [InstanceName] ,@ActiveNode AS [ComputerNamePhysicalNetBIOS] ,(CASE WHEN CONVERT([varchar](100),SERVERPROPERTY('IsClustered')) = 1 THEN 'Clustered' WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'Not Clustered' WHEN SERVERPROPERTY('IsClustered') = NULL THEN 'Error' END) AS [IsClustered] ,@ClusterNodes AS [ClusterNodes] ,@ActiveNode AS [ActiveNode] ,@IPAddress AS [HostIPAddress] ,@PortNumber AS [PortNumber] ,@AuthenticationMode AS [IsIntegratedSecurityOnly] ,(CASE WHEN @SQLServerAuditLevel = 0 THEN 'None.' WHEN @SQLServerAuditLevel = 1 THEN 'Successful Logins Only' WHEN @SQLServerAuditLevel = 2 THEN 'Failed Logins Only' WHEN @SQLServerAuditLevel = 3 THEN 'Both Failed and Successful Logins Only' ELSE 'N/A' END) AS [AuditLevel] ,CONVERT([varchar](100),SERVERPROPERTY('ProductVersion')) AS [ProductVersion] ,CONVERT([varchar](100),SERVERPROPERTY('ProductLevel')) AS [ProductLevel] ,ISNULL(CONVERT([varchar](100),SERVERPROPERTY('ResourceVersion')) ,CONVERT([varchar](100),SERVERPROPERTY('ProductVersion'))) AS [ResourceVersion] ,ISNULL(CONVERT([varchar](100),SERVERPROPERTY('ResourceLastUpdateDateTime')) ,'Information Not Available') AS [ResourceLastUpdateDateTime] ,CAST (SERVERPROPERTY('Edition') as [varchar](64)) AS [EngineEdition] ,ISNULL(CONVERT([varchar](100),SERVERPROPERTY('BuildClrVersion')), 'NOT Applicable') AS [BuildClrVersion] ,CONVERT([varchar](100),SERVERPROPERTY('Collation')) AS [Collation] ,CONVERT([varchar](100),SERVERPROPERTY('CollationID')) AS [CollationID] ,CONVERT([varchar](100),SERVERPROPERTY('ComparisonStyle')) AS [ComparisonStyle] ,(CASE WHEN CONVERT([varchar](100),SERVERPROPERTY('IsFullTextInstalled')) = 1 THEN 'Full-text is installed' WHEN SERVERPROPERTY('IsFullTextInstalled') = 0 THEN 'Full-text is not installed' WHEN SERVERPROPERTY('IsFullTextInstalled') = NULL THEN 'Error' END) AS [IsFullTextInstalled] ,ISNULL (CONVERT([varchar](100), SERVERPROPERTY('SqlCharSet')), 'No Information') AS [SQLCharset] ,ISNULL (CONVERT([varchar](100), SERVERPROPERTY('SqlCharSetName')), 'No Information') AS [SQLCharsetName] ,ISNULL (CONVERT([varchar](100), SERVERPROPERTY('SqlSortOrder')), 'No Information') AS [SQLSortOrderID] ,ISNULL (CONVERT([varchar](100), SERVERPROPERTY('SqlSortOrderName')), 'No Information') AS [SQLSortOrderName] ,(SELECT C_VALUE from [#_XPMSVER] where [C_NAME] = 'Platform') as [Platform] ,(SELECT C_VALUE from [#_XPMSVER] where [C_NAME] = 'FileDescription' ) as [FileDescription] ,(SELECT C_VALUE from [#_XPMSVER] where [C_NAME] = 'WindowsVersion') as [WindowsVersion] ,(SELECT INT_VALUE from [#_XPMSVER] where [C_NAME] = 'ProcessorCount') as [ProcessorCount] ,(SELECT ISNULL(C_VALUE,CAST (INT_VALUE AS VARCHAR(9))) from #_XPMSVER where [C_NAME] = 'ProcessorType') as [ProcessorType] ,(SELECT INT_VALUE from [#_XPMSVER] where [C_NAME] = 'PhysicalMemory') as [PhysicalMemory] ,@PageFile AS [ServerPageFile] ,@SQLServerInstallationLocation AS [SQLInstallationLocation] ,@BinariesPath AS [BinariesPath] ,@ErrorLogDirectory AS [ErrorLogsLocation] ,@SQLServerServiceAccount AS [MSSQLServerServiceStartupUser] ,@SQLAgentServiceAccount AS [MSSQLAgentServiceStartupUser] ,@SQLServerStartupType AS [MSSQLServerServiceStartupType] ,@SQLAgentStartupType AS [MSSQLAgentServiceStartupType] ,(SELECT [login_time] FROM [master]..[sysprocesses] WHERE [spid] = 1) AS [InstanceLastStartDate] -- Dropping temporary table IF OBJECT_ID('[Tempdb].[dbo].[#_IPCONFIG_OUTPUT]') IS NOT NULL DROP TABLE [dbo].[#_IPCONFIG_OUTPUT] IF OBJECT_ID('[Tempdb].[dbo].[#_PAGE_FILE_DETAILS]') IS NOT NULL DROP TABLE [dbo].[#_PAGE_FILE_DETAILS] IF OBJECT_ID('[Tempdb].[dbo].[#_XPMSVER]') IS NOT NULL DROP TABLE [dbo].[#_XPMSVER] GO
I hope you find it useful! 
