Home > Link, Microsoft SQL Server, MSBI, Optimization, Query, Script, SQL Mentalist, SQL PraRup, SQL Query, SQL Server, Technology,, TSQL, Vishal Pawar > BI SQL # 271 : SQL Server DBA Scripts : Display all Server Information

BI SQL # 271 : SQL Server DBA Scripts : Display all Server Information

Hi Folks,

In this article we are going to cover How to Display all Server Information.

In this post we are going to discuss following points:

  • Problem Statement of SQL Script:
  • Description of SQL Script:
  • SQL Script Output Column
  • SQL Script Code
  • User Level to execute

Problem Statement of SQL Script:

How to Display all Server Information ?

Description of SQL Script:

This script will Display all Server Information.

SQL Script Output Column

clip_image001 clip_image002

SQL Script Code

SET NOCOUNT ON;

DROP TABLE #Server_Info_SST

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

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')

----------
CREATE TABLE #Server_Info_SST (
    id INT IDENTITY(1, 1)
    ,Field VARCHAR(50)
    ,Detaila VARCHAR(MAX)
    )

INSERT INTO #Server_Info_SST (
    Field
    ,Detaila
    )
VALUES (
    'Domain'
    ,UPPER(@Domain)
    )
    ,(
    'SQLServerName'
    ,CONVERT([varchar](64), SERVERPROPERTY('ServerName'))
    )
    ,(
    'InstanceName'
    ,@FullInstanceName
    )
    ,(
    'ComputerNamePhysicalNetBIOS'
    ,@ActiveNode
    )
    ,(
    'IsClustered'
    ,(
        CASE 
            WHEN CONVERT([varchar](100), SERVERPROPERTY(
                        'IsClustered')) = 1
                THEN 'Clustered'
            WHEN SERVERPROPERTY('IsClustered') = 0
                THEN 'Not Clustered'
            WHEN SERVERPROPERTY('IsClustered') = NULL
                THEN 'Error'
            END
        )
    )
    ,(
    'ClusterNodes'
    ,@ClusterNodes
    )
    ,(
    'ActiveNode'
    ,@ActiveNode
    )
    ,(
    'HostIPAddress'
    ,@IPAddress
    )
    ,(
    'PortNumber'
    ,@PortNumber
    )
    ,(
    'IsIntegratedSecurityOnly'
    ,@AuthenticationMode
    )
    ,(
    'AuditLevel'
    ,(
        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
        )
    )
    ,(
    'ProductVersion'
    ,CONVERT([varchar](100), SERVERPROPERTY('ProductVersion')
    )
    )
    ,(
    'ProductLevel'
    ,CONVERT([varchar](100), SERVERPROPERTY('ProductLevel'))
    )
    ,(
    'ResourceVersion'
    ,ISNULL(CONVERT([varchar](100), SERVERPROPERTY(
                'ResourceVersion')), CONVERT([varchar](100), 
            SERVERPROPERTY('ProductVersion')))
    )
    ,(
    'ResourceLastUpdateDateTime'
    ,ISNULL(CONVERT([varchar](100), SERVERPROPERTY(
                'ResourceLastUpdateDateTime')), 
        'Information Not Available')
    )
    ,(
    'EngineEdition'
    ,CAST(SERVERPROPERTY('Edition') AS [varchar](64))
    )
    ,(
    'BuildClrVersion'
    ,ISNULL(CONVERT([varchar](100), SERVERPROPERTY(
                'BuildClrVersion')), 'NOT Applicable')
    )
    ,(
    'Collation'
    ,CONVERT([varchar](100), SERVERPROPERTY('Collation'))
    )
    ,(
    'CollationID'
    ,CONVERT([varchar](100), SERVERPROPERTY('CollationID'))
    )
    ,(
    'ComparisonStyle'
    ,CONVERT([varchar](100), SERVERPROPERTY('ComparisonStyle'
        ))
    )
    ,(
    'IsFullTextInstalled'
    ,(
        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
        )
    )
    ,(
    'SQLCharset'
    ,ISNULL(CONVERT([varchar](100), SERVERPROPERTY(
                'SqlCharSet')), 'No Information')
    )
    ,(
    'SQLCharsetName'
    ,ISNULL(CONVERT([varchar](100), SERVERPROPERTY(
                'SqlCharSetName')), 'No Information')
    )
    ,(
    'SQLSortOrderID'
    ,ISNULL(CONVERT([varchar](100), SERVERPROPERTY(
                'SqlSortOrder')), 'No Information')
    )
    ,(
    'SQLSortOrderName'
    ,ISNULL(CONVERT([varchar](100), SERVERPROPERTY(
                'SqlSortOrderName')), 'No Information')
    )
    ,(
    'Platform'
    ,(
        SELECT C_VALUE
        FROM [#_XPMSVER]
        WHERE [C_NAME] = 'Platform'
        )
    )
    ,(
    'FileDescription'
    ,(
        SELECT C_VALUE
        FROM [#_XPMSVER]
        WHERE [C_NAME] = 'FileDescription'
        )
    )
    ,(
    'WindowsVersion'
    ,CAST((
            SELECT C_VALUE
            FROM [#_XPMSVER]
            WHERE [C_NAME] = 'WindowsVersion'
            ) AS VARCHAR(50))
    )
    ,(
    'ProcessorCount'
    ,CAST((
            SELECT INT_VALUE
            FROM [#_XPMSVER]
            WHERE [C_NAME] = 'ProcessorCount'
            ) AS VARCHAR(50))
    )
    ,(
    'ProcessorType'
    ,(
        SELECT ISNULL(C_VALUE, CAST(INT_VALUE AS VARCHAR(9)))
        FROM #_XPMSVER
        WHERE [C_NAME] = 'ProcessorType'
        )
    )
    ,(
    'PhysicalMemory'
    ,CAST((
            SELECT INT_VALUE
            FROM [#_XPMSVER]
            WHERE [C_NAME] = 'PhysicalMemory'
            ) AS VARCHAR(50))
    )
    ,(
    'ServerPageFile'
    ,@PageFile
    )
    ,(
    'SQLInstallationLocation'
    ,@SQLServerInstallationLocation
    )
    ,(
    'BinariesPath'
    ,@BinariesPath
    )
    ,(
    'ErrorLogsLocation'
    ,@ErrorLogDirectory
    )
    ,(
    'MSSQLServerServiceStartupUser'
    ,@SQLServerServiceAccount
    )
    ,(
    'MSSQLAgentServiceStartupUser'
    ,@SQLAgentServiceAccount
    )
    ,(
    'MSSQLServerServiceStartupType'
    ,@SQLServerStartupType
    )
    ,(
    'MSSQLAgentServiceStartupType'
    ,@SQLAgentStartupType
    )
    ,(
    '[InstanceLastStartDate]'
    ,CONVERT(VARCHAR(20), (
            CONVERT(DATETIME, (
                    SELECT [login_time]
                    FROM [master]..[sysprocesses]
                    WHERE [spid] = 1
                    ))
            ))
    ) --INTO SQLServerInfo

SELECT *
FROM #Server_Info_SST

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

User Level to execute

300

    Hope you will like How to Display all Server Information.

    If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :

Link Resource Website

For More information related to BI World visit my Mentalist Blog

SQL Server Mentalist >> SQL Learning Blog

Business Intelligence Mentalist >> BI World

Infographic Mentalist >> Image worth explaining thousand Words

Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ

DBA Mentalist >>Advance SQL Server Blog

Microsoft BI Mentalist >> MS BI Development Update

Connect With me on

| FaceBook |Twitter | linkedIn| Google+ | WordPress | RSS |

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: