Metadata SERVERPROPERTY SQL function


1. Usage of SERVERPROPERTY

SQL Server Query 1

            
 SELECT   
SERVERPROPERTY('MachineName') AS MachineName,
SERVERPROPERTY('InstanceName') AS InstanceName,
SERVERPROPERTY('ServerName') AS ServerName,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion,
SERVERPROPERTY('ProductMinorVersion') AS ProductMinorVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductBuild') AS ProductBuild,
SERVERPROPERTY('Collation') AS Collation,
SERVERPROPERTY('IsIntegratedSecurityOnly') AS IsIntegratedSecurityOnly,
SERVERPROPERTY('EngineEdition') AS EngineEdition,
SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled,
SERVERPROPERTY('IsClustered') AS IsClustered,
SERVERPROPERTY('IsBigDataCluster') AS IsBigDataCluster,
SERVERPROPERTY('InstanceDefaultDataPath') AS InstanceDefaultDataPath,
SERVERPROPERTY('InstanceDefaultLogPath') AS InstanceDefaultLogPath,
SERVERPROPERTY('InstanceDefaultBackupPath') AS InstanceDefaultBackupPath,
SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,
SERVERPROPERTY('ResourceVersion') AS ResourceVersion,
SERVERPROPERTY('IsTempDbMetadataMemoryOptimized') AS IsTempDbMetadataMemoryOptimized,
SERVERPROPERTY('IsXTPSupported') AS IsXTPSupported,
SERVERPROPERTY('IsPolyBaseInstalled') AS IsPolyBaseInstalled,
SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled,
SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS IsAdvancedAnalyticsInstalled,
SERVERPROPERTY('FilestreamConfiguredLevel ') AS FilestreamConfiguredLevel ,
SERVERPROPERTY('HadrManagerStatus') AS HadrManagerStatus,
SERVERPROPERTY('IsLocalDB') AS IsLocalDB,
SERVERPROPERTY('IsSingleUser') AS IsSingleUser,
SERVERPROPERTY('BuildClrVersion') AS BuildClrVersion

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  
.Select()
.Column(new SERVERPROPERTY(ServerLevelProperty.MachineName), "MachineName")
.Column(new SERVERPROPERTY(ServerLevelProperty.InstanceName), "InstanceName")
.Column(new SERVERPROPERTY(ServerLevelProperty.ServerName), "ServerName")
.Column(new SERVERPROPERTY(ServerLevelProperty.Edition), "Edition")
.Column(new SERVERPROPERTY(ServerLevelProperty.ProductVersion), "ProductVersion")
.Column(new SERVERPROPERTY(ServerLevelProperty.ProductMajorVersion), "ProductMajorVersion")
.Column(new SERVERPROPERTY(ServerLevelProperty.ProductMinorVersion), "ProductMinorVersion")
.Column(new SERVERPROPERTY(ServerLevelProperty.ProductLevel), "ProductLevel")
.Column(new SERVERPROPERTY(ServerLevelProperty.ProductBuild), "ProductBuild")
.Column(new SERVERPROPERTY(ServerLevelProperty.Collation), "Collation")
.Column(new SERVERPROPERTY(ServerLevelProperty.IsIntegratedSecurityOnly), "IsIntegratedSecurityOnly")
.Column(new SERVERPROPERTY(ServerLevelProperty.EngineEdition), "EngineEdition")
.Column(new SERVERPROPERTY(ServerLevelProperty.IsHadrEnabled), "IsHadrEnabled")
.Column(new SERVERPROPERTY(ServerLevelProperty.IsClustered), "IsClustered")
.Column(new SERVERPROPERTY(ServerLevelProperty.IsBigDataCluster), "IsBigDataCluster")
.Column(new SERVERPROPERTY(ServerLevelProperty.InstanceDefaultDataPath), "InstanceDefaultDataPath")
.Column(new SERVERPROPERTY(ServerLevelProperty.InstanceDefaultLogPath), "InstanceDefaultLogPath")
.Column(new SERVERPROPERTY(ServerLevelProperty.InstanceDefaultBackupPath), "InstanceDefaultBackupPath")
.Column(new SERVERPROPERTY(ServerLevelProperty.ResourceLastUpdateDateTime), "ResourceLastUpdateDateTime")
.Column(new SERVERPROPERTY(ServerLevelProperty.ResourceVersion), "ResourceVersion")
.Column(new SERVERPROPERTY(ServerLevelProperty.IsTempDbMetadataMemoryOptimized), "IsTempDbMetadataMemoryOptimized")
.Column(new SERVERPROPERTY(ServerLevelProperty.IsXTPSupported), "IsXTPSupported")
.Column(new SERVERPROPERTY(ServerLevelProperty.IsPolyBaseInstalled), "IsPolyBaseInstalled")
.Column(new SERVERPROPERTY(ServerLevelProperty.IsFullTextInstalled), "IsFullTextInstalled")
.Column(new SERVERPROPERTY(ServerLevelProperty.IsAdvancedAnalyticsInstalled), "IsAdvancedAnalyticsInstalled")
.Column(new SERVERPROPERTY(ServerLevelProperty.FilestreamConfiguredLevel), "FilestreamConfiguredLevel")
.Column(new SERVERPROPERTY(ServerLevelProperty.HadrManagerStatus), "HadrManagerStatus")
.Column(new SERVERPROPERTY(ServerLevelProperty.IsLocalDB), "IsLocalDB")
.Column(new SERVERPROPERTY(ServerLevelProperty.IsSingleUser), "IsSingleUser")
.Column(new SERVERPROPERTY(ServerLevelProperty.BuildClrVersion), "BuildClrVersion")
.Build();

Query build by SqlQueryBuilder 1

            
SELECT SERVERPROPERTY(@pMAIN_2603210354440995530) AS MachineName,
       SERVERPROPERTY(@pMAIN_2603210354440995531) AS InstanceName,
       SERVERPROPERTY(@pMAIN_2603210354440995532) AS ServerName,
       SERVERPROPERTY(@pMAIN_2603210354440995533) AS Edition,
       SERVERPROPERTY(@pMAIN_2603210354440995534) AS ProductVersion,
       SERVERPROPERTY(@pMAIN_2603210354440995535) AS ProductMajorVersion,
       SERVERPROPERTY(@pMAIN_2603210354440995536) AS ProductMinorVersion,
       SERVERPROPERTY(@pMAIN_2603210354440995537) AS ProductLevel,
       SERVERPROPERTY(@pMAIN_2603210354440995538) AS ProductBuild,
       SERVERPROPERTY(@pMAIN_2603210354440995539) AS Collation,
       SERVERPROPERTY(@pMAIN_260321035444099553_10) AS IsIntegratedSecurityOnly,
       SERVERPROPERTY(@pMAIN_260321035444099553_11) AS EngineEdition,
       SERVERPROPERTY(@pMAIN_260321035444099553_12) AS IsHadrEnabled,
       SERVERPROPERTY(@pMAIN_260321035444099553_13) AS IsClustered,
       SERVERPROPERTY(@pMAIN_260321035444099553_14) AS IsBigDataCluster,
       SERVERPROPERTY(@pMAIN_260321035444099553_15) AS InstanceDefaultDataPath,
       SERVERPROPERTY(@pMAIN_260321035444099553_16) AS InstanceDefaultLogPath,
       SERVERPROPERTY(@pMAIN_260321035444099553_17) AS InstanceDefaultBackupPath,
       SERVERPROPERTY(@pMAIN_260321035444099553_18) AS ResourceLastUpdateDateTime,
       SERVERPROPERTY(@pMAIN_260321035444099553_19) AS ResourceVersion,
       SERVERPROPERTY(@pMAIN_260321035444099553_20) AS IsTempDbMetadataMemoryOptimized,
       SERVERPROPERTY(@pMAIN_260321035444099553_21) AS IsXTPSupported,
       SERVERPROPERTY(@pMAIN_260321035444099553_22) AS IsPolyBaseInstalled,
       SERVERPROPERTY(@pMAIN_260321035444099553_23) AS IsFullTextInstalled,
       SERVERPROPERTY(@pMAIN_260321035444099553_24) AS IsAdvancedAnalyticsInstalled,
       SERVERPROPERTY(@pMAIN_260321035444099553_25) AS FilestreamConfiguredLevel,
       SERVERPROPERTY(@pMAIN_260321035444099553_26) AS HadrManagerStatus,
       SERVERPROPERTY(@pMAIN_260321035444099553_27) AS IsLocalDB,
       SERVERPROPERTY(@pMAIN_260321035444099553_28) AS IsSingleUser,
       SERVERPROPERTY(@pMAIN_260321035444099553_29) AS BuildClrVersion;


            
        

Parameters (If used)

Name Value
@pMAIN_2603210354440995530 MachineName
@pMAIN_2603210354440995531 InstanceName
@pMAIN_2603210354440995532 ServerName
@pMAIN_2603210354440995533 Edition
@pMAIN_2603210354440995534 ProductVersion
@pMAIN_2603210354440995535 ProductMajorVersion
@pMAIN_2603210354440995536 ProductMajorVersion
@pMAIN_2603210354440995537 ProductLevel
@pMAIN_2603210354440995538 ProductBuild
@pMAIN_2603210354440995539 Collation
@pMAIN_260321035444099553_10 IsIntegratedSecurityOnly
@pMAIN_260321035444099553_11 EngineEdition
@pMAIN_260321035444099553_12 IsHadrEnabled
@pMAIN_260321035444099553_13 IsClustered
@pMAIN_260321035444099553_14 IsBigDataCluster
@pMAIN_260321035444099553_15 InstanceDefaultDataPath
@pMAIN_260321035444099553_16 InstanceDefaultLogPath
@pMAIN_260321035444099553_17 InstanceDefaultBackupPath
@pMAIN_260321035444099553_18 ResourceLastUpdateDateTime
@pMAIN_260321035444099553_19 ResourceVersion
@pMAIN_260321035444099553_20 IsTempDbMetadataMemoryOptimized
@pMAIN_260321035444099553_21 IsXTPSupported
@pMAIN_260321035444099553_22 IsPolyBaseInstalled
@pMAIN_260321035444099553_23 IsFullTextInstalled
@pMAIN_260321035444099553_24 IsAdvancedAnalyticsInstalled
@pMAIN_260321035444099553_25 FilestreamConfiguredLevel
@pMAIN_260321035444099553_26 HadrManagerStatus
@pMAIN_260321035444099553_27 IsLocalDB
@pMAIN_260321035444099553_28 IsSingleUser
@pMAIN_260321035444099553_29 BuildClrVersion

Query Results 1:

  MachineName InstanceName ServerName Edition ProductVersion ProductMajorVersion ProductMinorVersion ProductLevel ProductBuild Collation IsIntegratedSecurityOnly EngineEdition IsHadrEnabled IsClustered IsBigDataCluster InstanceDefaultDataPath InstanceDefaultLogPath InstanceDefaultBackupPath ResourceLastUpdateDateTime ResourceVersion IsTempDbMetadataMemoryOptimized IsXTPSupported IsPolyBaseInstalled IsFullTextInstalled IsAdvancedAnalyticsInstalled FilestreamConfiguredLevel HadrManagerStatus IsLocalDB IsSingleUser BuildClrVersion
1 WINSOME MSSQLSERVER2022 WINSOME\MSSQLSERVER2022 Express Edition (64-bit) 16.0.1000.6 16 16 RTM 1000 SQL_Latin1_General_CP1_CI_AS 0 4 0 0 0 C:\Program Files (x86)\Plesk\Databases\MSSQL\MSSQL16.MSSQLSERVER2022\MSSQL\DATA\ C:\Program Files (x86)\Plesk\Databases\MSSQL\MSSQL16.MSSQLSERVER2022\MSSQL\DATA\ C:\Program Files (x86)\Plesk\\Databases\MSSQL\MSSQL16.MSSQLSERVER2022\MSSQL\Backup 10/08/2022 06:32:07 16.00.1000 0 1 0 1 0 0 2 0 0 v4.0.30319