SQL Script to Compare SQL Server Settings

How to Compare SQL Server Settings ?
This is pretty simple, we can run sp_configure on individual server and can do the comparison manually but what if we can have a script where we just need to supply the servername, that that script will automatically do a comparison and let you know the results.
At times while debugging an issue or setting up a environment we have to compare the server configurations across the SQL Servers. So this script made that pretty easy.

-- Enable Server Remote adhoc queries at server level
sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
go
-- End of Server settings

      declare @ServerNames VARCHAR(1000)
set @ServerNames = '<Enter Server Names here> and use comma (,)as server delimiter'

/*
Parameters
@ServerNames = The servers configurations to compared should be passed in the parameter as a comma seperated value as shown in the below usage.
               This parameter cannot be blank. 

Usage : set @ServerNames ='Server1,Server2,Server3'

*/

   SET NOCOUNT ON

   IF LTRIM(RTRIM(@ServerNames)) = ''
   BEGIN
        RAISERROR('ServerNames cannot be empty',16,1)
        RETURN
   END

    DECLARE @ServerTbl TABLE
    (
     ServerName SYSNAME
    ,LocalServer bit DEFAULT 0
    ,Processed   bit DEFAULT 0
    )

    DECLARE @SQL         VARCHAR(MAX)
    DECLARE @BaseSQL     VARCHAR(MAX)
    DECLARE @Server         SYSNAME
    DECLARE @LocalServer BIT
    DECLARE @PivotServer VARCHAR(4000)

    SELECT @SQL = 'SELECT ''' + REPLACE (@ServerNames,',',''' UNION SELECT ''') + ''''

    INSERT INTO @ServerTbl
    (ServerName)
    EXEC (@SQL)

    UPDATE s
       SET s.LocalServer = 1
      FROM @ServerTbl s
     WHERE S.ServerName = @@SERVERNAME

    IF OBJECT_ID('tempdb.dbo.##ServerConfigTmp') IS NOT NULL
        DROP TABLE ##ServerConfigTmp

    CREATE TABLE ##ServerConfigTmp
    (
     Rnk          VARCHAR(500)
    ,ServerName      SYSNAME
    ,ConfigName   VARCHAR(200)
    ,Description  VARCHAR(500)
    ,Minimum      VARCHAR(500)
    ,Maximum      VARCHAR(500)
    ,Value          VARCHAR(500)
    )

    WHILE EXISTS(SELECT 1 FROM @ServerTbl WHERE Processed = 0)
    BEGIN

       SELECT TOP 1 @Server = ServerName,@LocalServer=LocalServer
         FROM @ServerTbl
        WHERE Processed = 0
        ORDER BY LocalServer DESC

        PRINT REPLICATE ('-',200)
        PRINT 'Processing ' + @server
        PRINT REPLICATE ('-',200)

       SELECT @SQL = 'EXEC (''SELECT Rnk=''''8'''',Servername=''''' + @Server + ''''',Name,description,CONVERT(VARCHAR(500),minimum),CONVERT(VARCHAR(500),maximum),CONVERT(VARCHAR(500),value_in_use)  FROM master.SYS.configurations
                    UNION
                    SELECT ''''1'''',''''' + @Server + ''''',''''ServerName'''' ,''''Server Name'''' ,'''''''','''''''',CONVERT(VARCHAR(500),SERVERPROPERTY(''''ServerName''''))
                    UNION
                    SELECT ''''2'''',''''' + @Server + ''''',''''InstanceName'''' , ''''Instance Name'''' ,'''''''','''''''',CONVERT(VARCHAR(500),SERVERPROPERTY(''''InstanceName''''))
                    UNION
                    SELECT ''''3'''',''''' + @Server + ''''',''''ProductVersion'''' , ''''Product version'''' ,'''''''','''''''',CONVERT(VARCHAR(500),SERVERPROPERTY(''''productversion''''))
                    UNION
                    SELECT ''''4'''',''''' + @Server + ''''',''''ProductLevel'''' , ''''Product Level'''' ,'''''''','''''''',CONVERT(VARCHAR(500),SERVERPROPERTY(''''productlevel''''))
                    UNION
                    SELECT ''''5'''',''''' + @Server + ''''',''''Edition'''' , ''''Edition'''' ,'''''''','''''''',CONVERT(VARCHAR(500),SERVERPROPERTY(''''edition''''))
                    UNION
                    SELECT ''''6'''',''''' + @Server + ''''',''''MachineName'''' , ''''Machine Name'''' ,'''''''','''''''',CONVERT(VARCHAR(500),SERVERPROPERTY(''''MachineName''''))
                    UNION
                    SELECT ''''7'''',''''' + @Server + ''''',''''LicenseType'''' , ''''License Type'''' ,'''''''','''''''',CONVERT(VARCHAR(500),SERVERPROPERTY(''''LicenseType''''))
                      '') '

        DECLARE @OpenRowsetSQL VARCHAR(MAX)

        SELECT @OpenRowsetSQL = 'SELECT a.*
                                 FROM OPENROWSET(''SQLNCLI'', ''Server=' + @Server + ';Trusted_Connection=yes;'',
                                ''' + replace(@SQL,'''','''''') + ''') AS a;'

        INSERT INTO ##ServerConfigTmp
        (Rnk,ServerName,ConfigName,Description,Minimum,Maximum,Value)
        EXEC(@OpenRowsetSQL)

        UPDATE @ServerTbl
           SET Processed = 1
         WHERE ServerName = @Server

    END

    SELECT @PivotServer = ''

    SELECT @PivotServer = @PivotServer + QUOTENAME(ServerName) + ','
      FROM @ServerTbl
     ORDER BY LocalServer DESC

    SELECT @PivotServer = SUBSTRING(@PivotServer,1,LEN(@PivotServer)-1)

    SELECT @SQL ='SELECT *
                    FROM ( SELECT rnk,Servername,Configname,Description,Value
                             FROM ##ServerConfigTmp) src
                             PIVOT (MAX(Value) FOR Servername IN (' + @PivotServer + ')) AS pvt
                            ORDER BY Rnk'

      EXEC(@SQL)

GO

-- Disable Server Remote adhoc queries at server level
sp_configure 'Ad Hoc Distributed Queries', 0
go
reconfigure
go
sp_configure 'show advanced options', 0
reconfigure
-- End of Server settings

OUTPUT Screenshot

 

Note : NULL will be displayed if config is not available in the server, in the value field.