Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

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! :)


Comments

Leave a comment on the original post [basitaalishan.com, opens in a new window]

Loading comments...