Discovering SQL Server Instance Details with SERVERPROPERTY
Ever needed to quickly grab essential information about your SQL Server instance? The `SERVERPROPERTY` function is your best friend! It allows you to retrieve a wide range of details, from server names and versions to specific configurations like collation and default paths. Below is a comprehensive SQL query that leverages `SERVERPROPERTY` to pull numerous valuable insights about your SQL Server environment.
SELECT SERVERPROPERTY('MachineName') AS val, 'Machine Name' AS Descr
UNION ALL SELECT SERVERPROPERTY('ServerName'), 'Server Name'
UNION ALL SELECT SERVERPROPERTY('InstanceName'), 'Instance'
UNION ALL SELECT SERVERPROPERTY('IsClustered'), 'IsClustered'
UNION ALL SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS'), 'Computer Name Physical NetBIOS'
UNION ALL SELECT SERVERPROPERTY('Edition'), 'Edition'
UNION ALL SELECT SERVERPROPERTY('ProductLevel'), 'Product Level (What servicing branch (RTM/SP/CU))'
UNION ALL SELECT SERVERPROPERTY('ProductUpdateLevel'), 'Product Update Level (Within a servicing branch, what CU# is applied)'
UNION ALL SELECT SERVERPROPERTY('ProductVersion'), 'Product Version'
UNION ALL SELECT SERVERPROPERTY('ProductMajorVersion'), 'Product Major Version'
UNION ALL SELECT SERVERPROPERTY('ProductMinorVersion'), 'Product Minor Version'
UNION ALL SELECT SERVERPROPERTY('ProductBuild'), 'Product Build'
UNION ALL SELECT SERVERPROPERTY('ProductBuildType'), 'Product Build Type ( Is this a GDR or OD hotfix (NULL if on a CU build))'
UNION ALL SELECT SERVERPROPERTY('ProductUpdateReference'), 'Product Update Reference (KB article number that is applicable for this build)'
UNION ALL SELECT SERVERPROPERTY('ProcessID'), 'ProcessID'
UNION ALL SELECT SERVERPROPERTY('Collation'), 'Collation'
UNION ALL SELECT SERVERPROPERTY('IsFullTextInstalled'), 'IsFullTextInstalled'
UNION ALL SELECT SERVERPROPERTY('IsIntegratedSecurityOnly'), 'IsIntegratedSecurityOnly'
UNION ALL SELECT SERVERPROPERTY('FilestreamConfiguredLevel'), 'FilestreamConfiguredLevel'
UNION ALL SELECT SERVERPROPERTY('IsHadrEnabled'), 'IsHadrEnabled'
UNION ALL SELECT SERVERPROPERTY('HadrManagerStatus'), 'HadrManagerStatus'
UNION ALL SELECT SERVERPROPERTY('InstanceDefaultDataPath'), 'InstanceDefaultDataPath'
UNION ALL SELECT SERVERPROPERTY('InstanceDefaultLogPath'), 'InstanceDefaultLogPath'
UNION ALL SELECT SERVERPROPERTY('BuildClrVersion'), 'Build CLR Version'
UNION ALL SELECT SERVERPROPERTY('IsXTPSupported'), 'IsXTPSupported'
UNION ALL SELECT SERVERPROPERTY('IsPolybaseInstalled'), 'IsPolybaseInstalled'
UNION ALL SELECT SERVERPROPERTY('IsAdvancedAnalyticsInstalled'), 'IsRServicesInstalled'
This query provides a detailed overview of your SQL Server setup, making it incredibly useful for inventory, troubleshooting, or just getting a quick snapshot of your environment.
Further Reading
For a complete and in-depth understanding of all the properties available with `SERVERPROPERTY`, check out the official Microsoft documentation.
