SQL Server Instance Configuration Summary Report

Problem

The requirement is to collect important SQL Server instance information such as the name of the instance, server's version, SQL Server edition, product edition, etc. all in a summarized and concentrated result set. The system function that returns property information about the server instance is SERVERPROPERTY, but this function returns only one value for each call. The presented function returns a collection of the most important SERVERPROPERY information and can easily be expanded, if needed.

Solution

My solution involves creating a T-SQL multi-statement table function in the SQL Server master database, called dbo.fn_DetailedServerInfo that will get the most important server level properties and display them in the function's returned result set. The function’s virtual result table will contain a property name and value columns.

The function will return about twenty different values such as the CLR version, cluster existence, collation name and ID, the character set name and id, sorting order, comparison type, computer BIOS name, full text search status, server name, license type, edition name level and version.

All of these values are gathered by calling the SERVERPROPERTY system function and by inserting these results into the function's virtual result table.

Here is the link for SERVERPROPERTY from MSDN (https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-2017 )

The result is a summarized report of all the SQL Server's important properties.

Here is the T-SQL code for the table function:

USE master
GO
-- =================================================================================
-- Author:         Eli Leiba
-- Create date:    06-2019
-- Procedure Name: dbo.fn_DetailedServerInfo
-- Description:    This Multi-Statement table function gathers all the most important server level properties  
-- ==================================================================================
CREATE FUNCTION dbo.fn_DetailedServerInfo ()
RETURNS @ServerProps TABLE (
   PropertyName NVARCHAR (128) NOT NULL,
   PropertyValue NVARCHAR (128) NULL
   )
AS
-- Body of the function
BEGIN
   INSERT INTO @ServerProps (<span style="width: 3.58pt; display: inline-block"></span>PropertyName,<span style="width: 1.79pt; display: inline-block"></span>PropertyValue<span style="width: 1.79pt; display: inline-block"></span>)
   SELECT 'Server Name',
      Convert (VARCHAR (128), SERVERPROPERTY ('Servername'))
   UNION ALL
   SELECT 'Product Version',
      Convert (VARCHAR (128), SERVERPROPERTY ('ProductVersion'))
   UNION ALL
   SELECT 'Product Level',
      Convert (VARCHAR (128), SERVERPROPERTY ('ProductLevel'))
   UNION ALL
   SELECT 'Resource Last Update Date Time',
      Convert (VARCHAR (128), SERVERPROPERTY ('ResourceLastUpdateDateTime'))
   UNION ALL
   SELECT 'Type of Security {Integrated/Mixed}',
      CASE 
              WHEN convert (VARCHAR (128), SERVERPROPERTY ('IsIntegratedSecurityOnly')) = 1 
                 THEN 'Integrated' 
              WHEN convert (VARCHAR (128), SERVERPROPERTY ('IsIntegratedSecurityOnly')) = 0 
                 THEN 'Mixed' END
   UNION ALL
   SELECT 'Server Edition',
      CASE WHEN convert (VARCHAR (128), SERVERPROPERTY ('EngineEdition')) = 1 
               THEN 'Personal Edition' 
                  WHEN convert (VARCHAR (128), SERVERPROPERTY ('EngineEdition')) = 2 
               THEN 'Standard Edition' 
                  WHEN convert (VARCHAR (128), SERVERPROPERTY ('EngineEdition')) = 3 
               THEN 'Enterprise Edition' 
                  WHEN convert (VARCHAR (128), SERVERPROPERTY ('EngineEdition')) = 4 
               THEN 'Express Edition' 
             END
   UNION ALL
   SELECT 'Instance Name',
      Convert (VARCHAR (128), SERVERPROPERTY ('InstanceName'))
   UNION ALL
   SELECT 'Computer Name in Physical Net BIOS',
      Convert (VARCHAR (128), SERVERPROPERTY ('ComputerNamePhysicalNetBIOS'))
   UNION ALL
   SELECT 'License Type',
      Convert (VARCHAR (128), SERVERPROPERTY ('LicenseType'))
   UNION ALL
   SELECT 'Build CLR Version',
      Convert (VARCHAR (128), SERVERPROPERTY ('BuildClrVersion'))
   UNION ALL
   SELECT 'Collation',
      Convert (VARCHAR (128), SERVERPROPERTY ('Collation'))
   UNION ALL
   SELECT 'Collation ID',
      Convert (VARCHAR (128), SERVERPROPERTY ('CollationID'))
   UNION ALL
   SELECT 'Comparison Style',
      Convert (VARCHAR (128), SERVERPROPERTY ('ComparisonStyle'))
   UNION ALL
   SELECT 'Product Edition',
      CASE WHEN convert (VARCHAR (128), SERVERPROPERTY ('EditionID')) = - 1253826760 
                    THEN 'Desktop Edition' 
                  WHEN convert (VARCHAR (128), SERVERPROPERTY ('EditionID')) = -1592396055 
                   THEN 'Express Edition' 
                 WHEN convert (VARCHAR (128), SERVERPROPERTY ('EditionID')) = -1534726760 
                   THEN 'Standard Edition' 
                 WHEN convert (VARCHAR (128), SERVERPROPERTY ('EditionID')) = 1333529388 
                   THEN 'Workgroup Edition' 
                 WHEN convert (VARCHAR (128), SERVERPROPERTY ('EditionID')) = 1804890536 
                   THEN 'Enterprise Edition' 
                 WHEN convert (VARCHAR (128), SERVERPROPERTY ('EditionID')) = -323382091 
                   THEN 'Personal Edition' 
                 WHEN convert (VARCHAR (128), SERVERPROPERTY ('EditionID')) = -2117995310 
                    THEN 'Developer Edition' 
                 WHEN convert (VARCHAR (128), SERVERPROPERTY ('EditionID')) = 610778273 
                    THEN 'Enterprise Evaluation Edition' 
                 WHEN convert (VARCHAR (128), SERVERPROPERTY ('EditionID')) = 1044790755 
                    THEN 'Windows Embedded SQL' 
                 WHEN convert (VARCHAR (128), SERVERPROPERTY ('EditionID')) = 4161255391 
                    THEN 'Express Edition with Advanced Services' 
              END
   UNION ALL
   SELECT 'Cluster Flag',
      CASE WHEN convert (VARCHAR (128), SERVERPROPERTY ('IsClustered')) = 1 THEN 'Clustered'              
              WHEN convert (VARCHAR (128), SERVERPROPERTY ('IsClustered')) = 0 THEN 'Not Clustered'    
              WHEN convert (VARCHAR (128), SERVERPROPERTY ('IsClustered')) IS NULL THEN 'Error' 
          END
   UNION ALL
   SELECT 'Full Text Flag',
      CASE WHEN convert (VARCHAR (128), SERVERPROPERTY ('IsFullTextInstalled')) = 1 
               THEN 'Full-text is installed' 
                  WHEN convert (VARCHAR (128), SERVERPROPERTY ('IsFullTextInstalled')) = 0 
               THEN 'Full-text is not installed' 
                  WHEN convert (VARCHAR (128), SERVERPROPERTY ('IsFullTextInstalled')) IS NULL 
               THEN 'Error' 
            END
   UNION ALL
   SELECT 'SQL Character set',
      Convert (VARCHAR (128), SERVERPROPERTY ('SqlCharSet'))
   UNION ALL
   SELECT 'Sql Character Set Name',
      Convert (VARCHAR (128), SERVERPROPERTY ('SqlCharSetName'))
   UNION ALL
   SELECT 'Sql Sort Order',
      Convert (VARCHAR (128), SERVERPROPERTY ('SqlSortOrder'))
   UNION ALL
   SELECT 'Sql Sort Order Name',
      Convert (VARCHAR (128), SERVERPROPERTY ('SqlSortOrderName'))
   RETURN
END
GO

Here is an example for using this function.

USE master
GO
SELECT * 
FROM dbo.fn_DetailedServerInfo()
ORDER BY PropertyName

And the results are (on my server):

property name

Next Steps

  • You can create and compile this table function in your master database and use it as a simple T-SQL tool for getting a detailed and concentrated SQL instance server properties report.
  • Other server properties can be easily added to this function as needed.
  • The function was tested on SQL Server 2014 and 2017, but should work with SQL Server 2005 and later.
  • Check out these other related tips:

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *