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_2606200150282140500) AS MachineName,
       SERVERPROPERTY(@pMAIN_2606200150282140501) AS InstanceName,
       SERVERPROPERTY(@pMAIN_2606200150282140502) AS ServerName,
       SERVERPROPERTY(@pMAIN_2606200150282140503) AS Edition,
       SERVERPROPERTY(@pMAIN_2606200150282140504) AS ProductVersion,
       SERVERPROPERTY(@pMAIN_2606200150282140505) AS ProductMajorVersion,
       SERVERPROPERTY(@pMAIN_2606200150282140506) AS ProductMinorVersion,
       SERVERPROPERTY(@pMAIN_2606200150282140507) AS ProductLevel,
       SERVERPROPERTY(@pMAIN_2606200150282140508) AS ProductBuild,
       SERVERPROPERTY(@pMAIN_2606200150282140509) AS Collation,
       SERVERPROPERTY(@pMAIN_260620015028214050_10) AS IsIntegratedSecurityOnly,
       SERVERPROPERTY(@pMAIN_260620015028214050_11) AS EngineEdition,
       SERVERPROPERTY(@pMAIN_260620015028214050_12) AS IsHadrEnabled,
       SERVERPROPERTY(@pMAIN_260620015028214050_13) AS IsClustered,
       SERVERPROPERTY(@pMAIN_260620015028214050_14) AS IsBigDataCluster,
       SERVERPROPERTY(@pMAIN_260620015028214050_15) AS InstanceDefaultDataPath,
       SERVERPROPERTY(@pMAIN_260620015028214050_16) AS InstanceDefaultLogPath,
       SERVERPROPERTY(@pMAIN_260620015028214050_17) AS InstanceDefaultBackupPath,
       SERVERPROPERTY(@pMAIN_260620015028214050_18) AS ResourceLastUpdateDateTime,
       SERVERPROPERTY(@pMAIN_260620015028214050_19) AS ResourceVersion,
       SERVERPROPERTY(@pMAIN_260620015028214050_20) AS IsTempDbMetadataMemoryOptimized,
       SERVERPROPERTY(@pMAIN_260620015028214050_21) AS IsXTPSupported,
       SERVERPROPERTY(@pMAIN_260620015028214050_22) AS IsPolyBaseInstalled,
       SERVERPROPERTY(@pMAIN_260620015028214050_23) AS IsFullTextInstalled,
       SERVERPROPERTY(@pMAIN_260620015028214050_24) AS IsAdvancedAnalyticsInstalled,
       SERVERPROPERTY(@pMAIN_260620015028214050_25) AS FilestreamConfiguredLevel,
       SERVERPROPERTY(@pMAIN_260620015028214050_26) AS HadrManagerStatus,
       SERVERPROPERTY(@pMAIN_260620015028214050_27) AS IsLocalDB,
       SERVERPROPERTY(@pMAIN_260620015028214050_28) AS IsSingleUser,
       SERVERPROPERTY(@pMAIN_260620015028214050_29) AS BuildClrVersion;


            
        

Parameters (If used)

Query Results 1: