View SQL Server information using Transact-SQL script

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! 🙂

11 thoughts on “View SQL Server information using Transact-SQL script”

  1. I had to modify one line in the final SELECT

    was :
    ,@PageFile AS [ServerPageFile]

    is now:
    ,REPLACE(REPLACE(@PageFile, CHAR(13) , ”), CHAR(10), ”) AS [ServerPageFile]

    Like

    Reply

  2. Excellent routine. However, I would get NULL for IP Address most of the time. The line of code:

    IF LEFT (CAST(SERVERPROPERTY(‘ProductVersion’)AS [sysname]), 5) = ‘10.50’

    Would only execute if the instance was SQL 2008 R2. Also will not execute the SQL instance was installed on a Windows 2003 server.

    I populated [#_XPMSVER] before the section to find IP address, added a variable for Windows version and used it in the IF statement instead.

    INSERT INTO [#_XPMSVER]
    EXEC( ‘master.dbo.xp_msver’)

    SELECT @OS = C_VALUE from [#_XPMSVER] where [C_NAME] = ‘WindowsVersion’

    –IF LEFT (CAST(SERVERPROPERTY(‘ProductVersion’)AS [sysname]), 5) = ‘10.50’
    IF LEFT (@OS, 2) = ‘6.’ — If the OS is Windows 2008 look for IPv4 Address

    Thanks again for writing and posting this excellent and very useful routine.

    Like

    Reply

  3. I agree with my fellow commenters, excellent routine. As a matter of fact, we use a routine with a similar purpose, which yields much of the same info, however, I did find quite a few useful additions in your routine, which I copied into our own routine (citing your name as the source, of course).

    With regards to the domain, I think there is a bug in your code. (Mind you, I’m not a Windows registry guru, so my suggestion to remedy it may also not be fully correct, but the current approach is buggy, anyway.) Most people will probably not hit it, though, but I was fortunate to run into it, thereby discovering the bug.

    In order to determine the network domain, you query the registry key @RegistryPath which translates to[SYSTEM\ControlSET001\Services\Tcpip\Parameters\]. However, it is quite possible for that registry key NOT to exist. For instance, in our clustered environment I encountered ControlSet002 and 003 and 004 but NOT 001. However, there should be no need to query any of the ControlSet00n keys, because we can just query the *CURRENT*ControlSet, which should always exist. Hence, the full key under HKEY_LOCAL_MACHINE then becomes [SYSTEM\CurrentControlSet\Services\Tcpip\Parameters].

    Or to put it ultrashort:
    change ControlSet001 to CurrentControlSet

    Thanks for all the good ideas and for sharing these!

    Like

    Reply

  4. Change

    SET @RegistryPath3 = N’SYSTEM\ControlSET001\Services\Tcpip\Parameters\’;

    to

    SET @RegistryPath3 = N’SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\’;

    because it might be that ControlSet001 (or indeed any other ControlSet00n) does not exist

    Like

    Reply

  5. @Lee Linares, No need to populate table beforehand! Left a comment, just use this..
    —— 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 ‘%IPv4 Address%’ –icruz: Changed ‘%IP Address%’ to ‘%IPv4 Address%’

    IF (ISNULL(@IPLine,’***’) != ‘***’)
    BEGIN
    SET @Position = CharIndex (‘:’, @IPLine, 1);
    SET @IPAddress = RTRIM(LTRIM(SUBSTRING(@IPLine
    ,@Position + 1,LEN(@IPLine) – @Position)))
    END
    END

    Like

    Reply

  7. Hello, first of all thanks a lot for sharing this excellent routine with us, I used this line for IP Address and I enabled sqlcmd for the query
    SET @IPAddress = (SELECT dec.local_net_address FROM sys.dm_exec_connections AS dec WHERE dec.session_id = @@SPID) since on some of my servers the xp_cmdshell is disabled. Thanks a lot.

    Like

    Reply

  8. Nice script! One more bug for IP Address: WHERE UPPER([IPConfigCommandOutput]) LIKE ‘%IPv4 Address%’
    Should be: WHERE UPPER ([IPConfigCommandOutput]) LIKE ‘%IPV4 ADDRESS%’
    Most people will probably not hit it, though, if you have case sensitive compare – then blank value returned.
    Also I have added a block of code to get Preferred Owners List for clustered instances (works for 2008-2012):

    DECLARE @PreferredOwnersList nvarchar(256)
    Set @PreferredOwnersList = ”

    IF SERVERPROPERTY(‘IsClustered’) = 1
    BEGIN
    —— Finding Prefered Owners ——–
    DECLARE @TableClusterGroups Table ([id] int identity(1,1), [ClusterGroup] sysname)
    DECLARE @InstanceNameNoInstance nvarchar(200)

    Set @InstanceNameNoInstance = Cast(SERVERPROPERTY(‘ServerName’) As nvarchar)
    Set @InstanceNameNoInstance =
    Case
    When Charindex(‘\’, @InstanceNameNoInstance, 1) > 0 Then Left(@InstanceNameNoInstance, Charindex(‘\’, @InstanceNameNoInstance, 1) – 1)
    Else @InstanceNameNoInstance
    End

    INSERT INTO @TableClusterGroups ([ClusterGroup])
    EXEC master..xp_instance_regenumkeys
    @rootkey = N’HKEY_LOCAL_MACHINE’,
    @key = N’Cluster\Groups\\’

    DECLARE @Counter int, @Counter2 int, @Sql nvarchar(max), @rootkey nvarchar(200), @returnValue nvarchar(500), @ClusterGroup sysname, @ClusterSubGroup sysname
    DECLARE @Name sysname, @rootkey2 nvarchar(200)

    DECLARE @Nodes as table([id] int identity(1,1), [Value] sysname)
    Insert Into @Nodes ([Value])
    Select ‘[‘ + NodeName + ‘]’ as [Value] FROM [sys].[dm_os_cluster_nodes]

    Set @Counter = 1
    WHILE @Counter < (Select ISNULL(max([id]), 0) + 1 From @TableClusterGroups)
    BEGIN

    Select @ClusterGroup = [ClusterGroup] From @TableClusterGroups Where [id] = @Counter
    Set @rootkey = N'Cluster\Groups\' + @ClusterGroup

    IF OBJECT_ID('[tempdb].[dbo].[#PreferredOwners]') IS NOT NULL
    DROP TABLE [dbo].[#PreferredOwners]
    Create Table [dbo].[#PreferredOwners] ([id] int identity(1,1), [Value] sysname, [Data] int, [Skip] sysname NULL)

    Insert Into [dbo].[#PreferredOwners] ([Value], [Data], [Skip])
    EXEC [master]..[xp_regread] 'HKEY_LOCAL_MACHINE'
    ,@rootkey
    ,@value_name = 'PreferredOwners'

    Set @PreferredOwnersList = NULL
    Select @PreferredOwnersList= COALESCE(@PreferredOwnersList+', ' ,'') + cast(n.Value as varchar)
    From [dbo].[#PreferredOwners] po Join @Nodes n on n.id = po.Data
    Order By po.id

    IF OBJECT_ID('[tempdb].[dbo].[#Contains]') IS NOT NULL
    DROP TABLE [dbo].[#Contains]
    Create Table [dbo].[#Contains] ([id] int identity(1,1), [Value] sysname, [Value2] sysname, [Skip] sysname NULL)

    Insert Into [dbo].[#Contains] ([Value], [Value2], [Skip])
    EXEC [master]..[xp_regread] 'HKEY_LOCAL_MACHINE'
    ,@rootkey
    ,@value_name = 'Contains'

    Set @Counter2 = 1

    WHILE @Counter2 0
    BEGIN
    Set @Counter = 1000 — exit early!
    BREAK
    END
    Set @Counter2 = @Counter2 + 1
    END
    Set @Counter = @Counter + 1
    END

    END

    Like

    Reply

  9. didn’t paste right, the script should end like this:

    WHILE @Counter2 0
    BEGIN
    Set @Counter = 1000 — exit early!
    BREAK
    END
    Set @Counter2 = @Counter2 + 1
    END
    Set @Counter = @Counter + 1
    END

    END

    Like

    Reply

  10. no love again! trying last time:
    WHILE @Counter2 0
    BEGIN
    Set @Counter = 1000 — exit early!
    BREAK
    END
    Set @Counter2 = @Counter2 + 1
    END
    Set @Counter = @Counter + 1
    END

    END

    Like

    Reply

Leave a comment