Have you ever wanted to know basic information about your SQL server? You can use the SERVERPROPERTY command to retrieve that information with a query.
Note that SERVERPROPERTY(ComputerNamePhysicalNetBIOS) will return the Physical Name of the active server. If you are on a cluster server, this will be different from the server name. If it is not on a cluster server, It will return the Server Name.
SELECT
SERVERPROPERTY('servername') as 'Server Name',
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as 'Physical Name (Cluster)',
SERVERPROPERTY('productversion') as 'Product Version',
SERVERPROPERTY('productlevel') as 'Service Pack',
SERVERPROPERTY('engineedition') as 'Engine Edition',
SERVERPROPERTY('edition') as 'Edition',
CASE
WHEN SERVERPROPERTY('IsFullTextInstalled') = 1 THEN 'Yes'
ELSE 'No'
END as 'Full Text Installed',
CASE
WHEN SERVERPROPERTY('IsSingleUser') = 1 THEN 'Yes'
ELSE 'No'
END as 'Single User Mode',
CASE
WHEN SERVERPROPERTY('isclustered') = 1 THEN 'Yes'
ELSE 'No'
END as 'Clustered',
CASE
WHEN SERVERPROPERTY('instancename') IS null THEN 'Default'
ELSE SERVERPROPERTY('instancename')
END AS 'Instance'
The table below is an example of the results you might get from the query above.
| Property | Value |
|---|---|
| Server Name | SQLSvr |
| Physical Name (Cluster) | SQLCL01 |
| Product Version | 10.50.2500.0 |
| Service Pack | SP1 |
| Engine Edition | 3 |
| Edition | Enterprise Edition (64-bit) |
| Full Text Installed | Yes |
| Single User Mode | No |
| Clustered | Yes |
| Instance | Default |