Below is a (slightly) improved script that returns all properties from the SERVERPROPERTY() function in SQL Server.
This is similar to the quick script that I posted recently. The difference is that for some properties, the script below uses CASE statements to provide a short explanation of the configured value, rather than the raw value (which is often a “1” or “0”). The aforementioned quick script, on the other hand, simply outputs the raw data for all rows.
The Script
SELECT 'BuildClrVersion' AS Property, SERVERPROPERTY('BuildClrVersion') AS Value
UNION ALL
SELECT 'Collation', SERVERPROPERTY('Collation')
UNION ALL
SELECT 'CollationID', SERVERPROPERTY('CollationID')
UNION ALL
SELECT 'ComparisonStyle', SERVERPROPERTY('ComparisonStyle')
UNION ALL
SELECT 'ComputerNamePhysicalNetBIOS', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
UNION ALL
SELECT 'Edition', SERVERPROPERTY('Edition')
UNION ALL
SELECT 'EditionID', SERVERPROPERTY('EditionID')
UNION ALL
SELECT 'EngineEdition',
CASE
WHEN SERVERPROPERTY('EngineEdition') = 1 THEN 'Personal or Desktop Engine (Not available in SQL Server 2005 (9.x) and later versions.)'
WHEN SERVERPROPERTY('EngineEdition') = 2 THEN 'Standard (This is returned for Standard, Web, and Business Intelligence.)'
WHEN SERVERPROPERTY('EngineEdition') = 3 THEN 'Enterprise (This is returned for Evaluation, Developer, and Enterprise editions.)'
WHEN SERVERPROPERTY('EngineEdition') = 4 THEN 'Express (This is returned for Express, Express with Tools, and Express with Advanced Services)'
WHEN SERVERPROPERTY('EngineEdition') = 5 THEN 'SQL Database'
WHEN SERVERPROPERTY('EngineEdition') = 6 THEN 'SQL Data Warehouse'
WHEN SERVERPROPERTY('EngineEdition') = 8 THEN 'Managed Instance'
ELSE SERVERPROPERTY('EngineEdition')
END
UNION ALL
SELECT 'HadrManagerStatus',
CASE
WHEN @@VERSION LIKE '%2000%8.%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) and later'
WHEN @@VERSION LIKE '%2005%9.%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) and later'
WHEN @@VERSION LIKE '%2008%10.0%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) and later'
WHEN @@VERSION LIKE '%2008 R2%10.5%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) and later'
WHEN SERVERPROPERTY('HadrManagerStatus') = 0 THEN 'Not started, pending communication'
WHEN SERVERPROPERTY('HadrManagerStatus') = 1 THEN 'Started and running'
WHEN SERVERPROPERTY('HadrManagerStatus') = 2 THEN 'Not started and failed'
ELSE SERVERPROPERTY('HadrManagerStatus')
END
UNION ALL
SELECT 'InstanceDefaultDataPath',
CASE
WHEN @@VERSION LIKE '%2000%8.%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) through current version in updates beginning in late 2015'
WHEN @@VERSION LIKE '%2005%9.%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) through current version in updates beginning in late 2015'
WHEN @@VERSION LIKE '%2008%10.0%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) through current version in updates beginning in late 2015'
WHEN @@VERSION LIKE '%2008 R2%10.5%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) through current version in updates beginning in late 2015'
ELSE SERVERPROPERTY('InstanceDefaultDataPath')
END
UNION ALL
SELECT 'InstanceDefaultLogPath',
CASE
WHEN @@VERSION LIKE '%2000%8.%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) through current version in updates beginning in late 2015'
WHEN @@VERSION LIKE '%2005%9.%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) through current version in updates beginning in late 2015'
WHEN @@VERSION LIKE '%2008%10.0%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) through current version in updates beginning in late 2015'
WHEN @@VERSION LIKE '%2008 R2%10.5%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) through current version in updates beginning in late 2015'
ELSE SERVERPROPERTY('InstanceDefaultLogPath')
END
UNION ALL
SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
UNION ALL
SELECT 'IsAdvancedAnalyticsInstalled',
CASE
WHEN SERVERPROPERTY('IsAdvancedAnalyticsInstalled') = 0 THEN 'No (Advanced Analytics feature was not installed during setup)'
WHEN SERVERPROPERTY('IsAdvancedAnalyticsInstalled') = 1 THEN 'Yes (Advanced Analytics feature was installed during setup)'
END
UNION ALL
SELECT 'IsClustered',
CASE
WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'No (This server instance is not configured in a failover cluster)'
WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'Yes (This server instance is configured in a failover cluster)'
END
UNION ALL
SELECT 'IsFullTextInstalled',
CASE
WHEN SERVERPROPERTY('IsFullTextInstalled') = 0 THEN 'No (The full-text and semantic indexing components are not installed on the current instance of SQL Server)'
WHEN SERVERPROPERTY('IsFullTextInstalled') = 1 THEN 'Yes (The full-text and semantic indexing components are installed on the current instance of SQL Server)'
END
UNION ALL
SELECT 'IsHadrEnabled',
CASE
WHEN @@VERSION LIKE '%2000%8.%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) and later'
WHEN @@VERSION LIKE '%2005%9.%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) and later'
WHEN @@VERSION LIKE '%2008%10.0%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) and later'
WHEN @@VERSION LIKE '%2008 R2%10.5%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) and later'
WHEN SERVERPROPERTY('IsHadrEnabled') = 0 THEN 'No (Always On availability groups is not enabled on this server instance)'
WHEN SERVERPROPERTY('IsHadrEnabled') = 1 THEN 'Yes (Always On availability groups is enabled on this server instance)'
END
UNION ALL
SELECT 'IsIntegratedSecurityOnly',
CASE
WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 0 THEN 'No (The server is not in integrated security mode)'
WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 1 THEN 'Yes (The server is in integrated security mode)'
END
UNION ALL
SELECT 'IsLocalDB',
CASE
WHEN @@VERSION LIKE '%2000%8.%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) and later'
WHEN @@VERSION LIKE '%2005%9.%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) and later'
WHEN @@VERSION LIKE '%2008%10.0%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) and later'
WHEN @@VERSION LIKE '%2008 R2%10.5%' THEN 'Note: This property only applies to SQL Server 2012 (11.x) and later'
WHEN SERVERPROPERTY('IsLocalDB') = 0 THEN 'No (This server is not an instance of SQL Server Express LocalDB)'
WHEN SERVERPROPERTY('IsLocalDB') = 1 THEN 'Yes (This server is an instance of SQL Server Express LocalDB)'
END
UNION ALL
SELECT 'IsPolyBaseInstalled',
CASE
WHEN SERVERPROPERTY('IsPolyBaseInstalled') = 0 THEN 'No (PolyBase feature is not installed)'
WHEN SERVERPROPERTY('IsPolyBaseInstalled') = 1 THEN 'Yes (PolyBase feature is installed)'
END
UNION ALL
SELECT 'IsSingleUser',
CASE
WHEN SERVERPROPERTY('IsSingleUser') = 0 THEN 'No (Server is not in single-user mode)'
WHEN SERVERPROPERTY('IsSingleUser') = 1 THEN 'Yes (Server is in single-user mode)'
END
UNION ALL
SELECT 'IsXTPSupported',
CASE
WHEN @@VERSION LIKE '%2000%8.%' THEN 'Note: This property only applies to SQL Server 2014 (12.x) and later), and SQL Database'
WHEN @@VERSION LIKE '%2005%9.%' THEN 'Note: This property only applies to SQL Server 2014 (12.x) and later), and SQL Database'
WHEN @@VERSION LIKE '%2008%10.0%' THEN 'Note: This property only applies to SQL Server 2014 (12.x) and later), and SQL Database'
WHEN @@VERSION LIKE '%2008 R2%10.5%' THEN 'Note: This property only applies to SQL Server 2014 (12.x) and later), and SQL Database'
WHEN @@VERSION LIKE '%2012%11.%' THEN 'Note: This property only applies to SQL Server 2014 (12.x) and later), and SQL Database'
WHEN SERVERPROPERTY('IsXTPSupported') = 0 THEN 'No (This server does not support In-Memory OLTP)'
WHEN SERVERPROPERTY('IsXTPSupported') = 1 THEN 'Yes (This server supports In-Memory OLTP)'
END
UNION ALL
SELECT 'LCID', SERVERPROPERTY('LCID')
UNION ALL
SELECT 'LicenseType', SERVERPROPERTY('LicenseType')
UNION ALL
SELECT 'MachineName', SERVERPROPERTY('MachineName')
UNION ALL
SELECT 'NumLicenses', ISNULL(SERVERPROPERTY('NumLicenses'), 'Unused. License information is not preserved or maintained by the SQL Server product. Always returns NULL.')
UNION ALL
SELECT 'ProcessID', SERVERPROPERTY('ProcessID')
UNION ALL
SELECT 'ProductBuild', SERVERPROPERTY('ProductBuild')
UNION ALL
SELECT 'ProductBuildType',
CASE
WHEN SERVERPROPERTY('ProductBuildType') = 'OD' THEN 'OD (On Demand - released for a specific customer)'
WHEN SERVERPROPERTY('ProductBuildType') = 'GDR' THEN '(General Distribution Release - released through windows update)'
END
UNION ALL
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
UNION ALL
SELECT 'ProductMajorVersion', SERVERPROPERTY('ProductMajorVersion')
UNION ALL
SELECT 'ProductMinorVersion', SERVERPROPERTY('ProductMinorVersion')
UNION ALL
SELECT 'ProductUpdateLevel', SERVERPROPERTY('ProductUpdateLevel')
UNION ALL
SELECT 'ProductUpdateReference', SERVERPROPERTY('ProductUpdateReference')
UNION ALL
SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
UNION ALL
SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
UNION ALL
SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
UNION ALL
SELECT 'ServerName', SERVERPROPERTY('ServerName')
UNION ALL
SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')
UNION ALL
SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')
UNION ALL
SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')
UNION ALL
SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')
UNION ALL
SELECT 'FilestreamShareName', SERVERPROPERTY('FilestreamShareName')
UNION ALL
SELECT 'FilestreamConfiguredLevel',
CASE
WHEN SERVERPROPERTY('FilestreamConfiguredLevel') = 0 THEN '0 (Disables FILESTREAM support for this instance)'
WHEN SERVERPROPERTY('FilestreamConfiguredLevel') = 1 THEN '1 (Enables FILESTREAM for Transact-SQL access)'
WHEN SERVERPROPERTY('FilestreamConfiguredLevel') = 2 THEN '2 (Enables FILESTREAM for Transact-SQL and Win32 streaming access)'
ELSE SERVERPROPERTY('FilestreamConfiguredLevel')
END
UNION ALL
SELECT 'FilestreamEffectiveLevel',
CASE
WHEN SERVERPROPERTY('FilestreamConfiguredLevel') = 0 THEN '0 (Disables FILESTREAM support for this instance)'
WHEN SERVERPROPERTY('FilestreamConfiguredLevel') = 1 THEN '1 (Enables FILESTREAM for Transact-SQL access)'
WHEN SERVERPROPERTY('FilestreamConfiguredLevel') = 2 THEN '2 (Enables FILESTREAM for Transact-SQL and Win32 streaming access)'
ELSE SERVERPROPERTY('FilestreamConfiguredLevel')
END;
Sample Results
Here are the results I get when running the above script on one of my test servers.
+------------------------------+-------------------------------------------------------------------------------------------------------------+ | Property | Value | |------------------------------+-------------------------------------------------------------------------------------------------------------| | BuildClrVersion | v4.0.30319 | | Collation | SQL_Latin1_General_CP1_CI_AS | | CollationID | 872468488 | | ComparisonStyle | 196609 | | ComputerNamePhysicalNetBIOS | sqlserver007 | | Edition | Developer Edition (64-bit) | | EditionID | -2117995310 | | EngineEdition | Enterprise (This is returned for Evaluation, Developer, and Enterprise editions.) | | HadrManagerStatus | Started and running | | InstanceDefaultDataPath | /var/opt/mssql/data/ | | InstanceDefaultLogPath | /var/opt/mssql/data/ | | InstanceName | NULL | | IsAdvancedAnalyticsInstalled | Yes (Advanced Analytics feature was installed during setup) | | IsClustered | No (This server instance is not configured in a failover cluster) | | IsFullTextInstalled | No (The full-text and semantic indexing components are not installed on the current instance of SQL Server) | | IsHadrEnabled | No (Always On availability groups is not enabled on this server instance) | | IsIntegratedSecurityOnly | No (The server is not in integrated security mode) | | IsLocalDB | No (This server is not an instance of SQL Server Express LocalDB) | | IsPolyBaseInstalled | No (PolyBase feature is not installed) | | IsSingleUser | No (Server is not in single-user mode) | | IsXTPSupported | Yes (This server supports In-Memory OLTP) | | LCID | 1033 | | LicenseType | DISABLED | | MachineName | sqlserver007 | | NumLicenses | Unused. License information is not preserved or maintained by the SQL Server product. Always returns NULL. | | ProcessID | 28 | | ProductBuild | 1800 | | ProductBuildType | NULL | | ProductLevel | CTP3.2 | | ProductMajorVersion | 15 | | ProductMinorVersion | 0 | | ProductUpdateLevel | NULL | | ProductUpdateReference | NULL | | ProductVersion | 15.0.1800.32 | | ResourceLastUpdateDateTime | 2019-07-17 22:15:31 | | ResourceVersion | 15.00.1800 | | ServerName | sqlserver007 | | SqlCharSet | 1 | | SqlCharSetName | iso_1 | | SqlSortOrder | 52 | | SqlSortOrderName | nocase_iso | | FilestreamShareName | MSSQLSERVER | | FilestreamConfiguredLevel | 0 (Disables FILESTREAM support for this instance) | | FilestreamEffectiveLevel | 0 (Disables FILESTREAM support for this instance) | +------------------------------+-------------------------------------------------------------------------------------------------------------+
Also see the Microsoft documentation for a more detailed description of each property.