SQL Server Instance Configuration Summary Report

By: Eli Leiba | Comments (1) | Related: > SQL Server Configurations

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 (PropertyName,PropertyValue)
   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):

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:
- Additional SERVERPROPERTY properties in SQL Server
- SQL Server Configuration Settings Query

About the author

Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, August 6, 2019 - 9:36:57 AM - Kenney Hill

An interesting idea. I will likely add some of your properties to the script that I cobbled together years back and occasionally add new bits and pieces to. My version puts everything in one row so that I can run it against multiple servers at once, I use Registered Servers in SSMS, so that I can see all of their info in a spreadsheet like format. Here is my script. I apologize for it's haphazardness, remember it is cobbled together, and it's been cutomized to what I want to see, like in the case of # of DBs. In theory this should work back to SQL 7 but I have not tested that, I am confident that it works back to at least 2005 though.

----------------------------------------------------------------
set nocount off;
 
DECLARE @ver nvarchar(128);
 
declare @edition varchar(128);
declare @prodlvl varchar(128);
 
 
set @ver = cast(serverproperty('ProductVersion') AS nvarchar(128));
set @edition = cast(SERVERPROPERTY('edition') as varchar(128));
set @prodlvl = cast(SERVERPROPERTY('productlevel') as varchar(128));
 
declare @subver nvarchar(128);
 
set @subver = SUBSTRING(@ver, CHARINDEX('.', @ver) + 1, 1);
set @ver = left(@ver, CHARINDEX('.', @ver) - 1);
 
declare @rgedition INTEGER;
declare @rgversion VARCHAR(16);
declare @rgserialnumber VARCHAR(30);
 
 
if exists (select top 1 1 from master.dbo.sysobjects where xtype = 'X' and name = 'sqbutility')
exec master..sqbutility 21, @rgedition OUTPUT, @rgversion OUTPUT, @rgserialnumber OUTPUT;
 
declare @physicalmemory DECIMAL(7,1);
declare @sqltext NVARCHAR(4000);
 
if cast(@ver as int) >= 11 --for SQL 2012 or higher?
set @sqltext = 'select @pm = cast(round(physical_memory_kb / 1024 / 1024.0 , 1)as decimal(7, 1)) from sys.dm_os_sys_info';
if cast(@ver as int) < 11 --for SQL before 2012
set @sqltext = 'select @pm = cast(round(physical_memory_in_bytes / 1024 / 1024 / 1024.0 , 1)as decimal(7, 1)) from sys.dm_os_sys_info';
 
exec sp_executesql @sqltext, N'@pm DECIMAL(7,1) OUTPUT', @pm = @physicalmemory OUTPUT;
 
declare @bulocation varchar(256);
 
--if object_id('master.dbo.SQLBackupOptions') is not null
--begin
-- select top 1
-- @bulocation = REPLACE( case when RIGHT(BackupPath, 1) = '\' then LEFT(BackupPath, LEN(BackupPath) - 1) else BackupPath end,
-- '\' + CAST(SERVERPROPERTY('servername') as varchar(128)), '')
-- from
-- [master].[dbo].[SQLBackupOptions];
--end
 
DECLARE
@MaxDoP Varchar(2),
@CostThresholdForParallelism Varchar(20);
 
SELECT
@MaxDoP = CAST(value_in_use AS Varchar(2))
FROM
sys.configurations
WHERE
name = 'max degree of parallelism';
 
SELECT
@CostThresholdForParallelism = CAST(value_in_use AS Varchar(20))
FROM
sys.configurations
WHERE
name = 'cost threshold for parallelism';
 
WITH
ServiceInfo AS
(
SELECT
@@SERVERNAME AS ServerName,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Server' THEN ServerServices.is_clustered END) IsClustered,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Server' THEN ServerServices.cluster_nodename END) ClusterNodeName,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Server' THEN ServerServices.service_account END) SQLServerServiceAccount,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Server' THEN ServerServices.startup_type_desc END) SQLServerStartupType,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Server' THEN ServerServices.status_desc END) SQLServerStatus,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Server Agent' THEN ServerServices.service_account END) SQLAgentServiceAccount,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Server Agent' THEN ServerServices.startup_type_desc END) SQLAgentStartupType,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Server Agent' THEN ServerServices.status_desc END) SQLAgentStatus,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Full-text Filter Daemon Launcher' THEN ServerServices.service_account END) SQLFullTextServiceAccount,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Full-text Filter Daemon Launcher' THEN ServerServices.startup_type_desc END) SQLFullTextStartupType,
MIN(CASE LEFT(ServerServices.servicename, CHARINDEX(' (', ServerServices.servicename) - 1) WHEN 'SQL Full-text Filter Daemon Launcher' THEN ServerServices.status_desc END) SQLFullTextStatus--,
FROM
sys.dm_server_services ServerServices
)
 
SELECT
SERVERPROPERTY('machinename') as "Server",
ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLSERVER') as "Instance", --select SERVERPROPERTY('ServerName')
(select local_net_address from sys.dm_exec_connections where session_id = @@SPID) as "IP",
(select local_tcp_port from sys.dm_exec_connections where session_id = @@SPID) as "Port",
cast(serverproperty('ProductVersion') AS nvarchar(128)) as "Version Number",
CASE @ver
WHEN '7'THEN '7'
WHEN '8'THEN '2000'
WHEN '9'THEN '2005'
WHEN '10'THEN '2008'
WHEN '11'THEN '2012'
WHEN '12'THEN '2014'
WHEN '13'THEN '2016'
WHEN '14'THEN '2017'
ELSE CAST(((CAST(@ver AS INT) - 14) * 2 + 2017) AS NVARCHAR(128)) + '?' --'UNKNOWN' - this formula assumes a new release every 2 years
END +
CASE @subver
WHEN '5'THEN 'R2'
ELSE ''
END as "Version",
left(@edition, charindex(' ', @edition) - 1) as "Edition",
REPLACE(REPLACE(@prodlvl, 'SP', ''), 'RTM', '0') as "SP", --@prodlvl AS SP,
--case when right(@edition, 8) = '(64-bit)' then 64 else 32 end as "Bits",
(select count(*) from sys.sysdatabases where dbid > 4 /*and name not in ('SQLPERF', 'SQLCARE')*/) as "# of DBs",
(select cpu_count /*/ hyperthread_ratio*/ from sys.dm_os_sys_info) as "CPUs",
--(select hyperthread_ratio from sys.dm_os_sys_info) as "Hyperthread Ratio",
@physicalmemory as "Mem(GB)",
(SELECT cast(round(cast(value as int) / 1024.0, 1) as decimal(12, 1))
FROM sys.configurations
WHERE name like 'max server memory%') as "SQL Mem(GB)",
RIGHT(@@version, LEN(@@version)- 3 - charindex (' ON ', UPPER(@@VERSION))) as "Windows Version",
@MaxDoP AS MaxDoP,
@CostThresholdForParallelism AS CostThreshold,
servinfo.IsClustered,
servinfo.ClusterNodeName,
servinfo.SQLServerServiceAccount,
servinfo.SQLServerStartupType,
servinfo.SQLServerStatus,
servinfo.SQLAgentServiceAccount,
servinfo.SQLAgentStartupType,
servinfo.SQLAgentStatus,
servinfo.SQLFullTextServiceAccount,
servinfo.SQLFullTextStartupType,
servinfo.SQLFullTextStatus,
--@bulocation as "Backup Location",
case when (select top 1 1 from master.dbo.sysobjects where xtype = 'X' and name = 'sqlbackup') = 1 then 'Red-Gate'
else 'Native' end as "Backup Type",
case @rgedition
when 0 THEN 'Trial: Expired'
when 1 THEN 'Trial'
when 2 THEN 'Standard'
when 3 THEN 'Professional'
when 6 THEN 'Lite'
else '' end as "Red Gate Edition",
isnull(@rgversion, '') as "Red Gate Version",
isnull(@rgserialnumber, '') as "Red Gate License"
FROM
ServiceInfo servinfo;