SERVERPROPERTY
Returns property information about the server instance.
Syntax
SERVERPROPERTY ( propertyname )
Arguments
propertyname
Is an expression containing the property information to be returned for the server. propertyname can be one of these values.
| Property name | Values returned |
|---|---|
| Collation | The name of the default collation for the server.
Returns NULL if invalid input or error. Base data type: nvarchar |
| Edition | The edition of the Microsoft® SQL Server™ instance installed on the server.
Returns: 'Desktop Engine' Base data type: nvarchar(128) |
| Engine Edition | The engine edition of the SQL Server instance installed on the server.
1 = Personal or Desktop Engine Base data type: int |
| InstanceName | The name of the instance to which the user is connected.
Returns NULL if the instance name is the default instance, or invalid input or error. Base data type: nvarchar |
| IsClustered | The server instance is configured in a failover cluster.
1 = Clustered. Base data type: int |
| IsFullTextInstalled | The full-text component is installed with the current instance of SQL Server.
1 = Full-text is installed. Base data type: int |
| IsIntegratedSecurityOnly | The server is in integrated security mode.
1 = Integrated Security. Base data type: int |
| IsSingleUser | The server is in single user mode.
1 = Single User. Base data type: int |
| IsSyncWithBackup | The database is either a published database or a distribution database, and can be restored without disrupting transactional replication.
1 = True. Base data type: int |
| LicenseType | Mode of this instance of SQL Server.
PER_SEAT = Per-seat mode Base data type: nvarchar(128) |
| MachineName | Windows NT computer name on which the server instance is running.
For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Server, it returns the name of the virtual server. Returns NULL if invalid input or error. Base data type: nvarchar |
| NumLicenses | Number of client licenses registered for this instance of SQL Server, if in per-seat mode.
Number of processors licensed for this instance of SQL Server, if in per-processor mode. Returns NULL if the server is none of the above. Base data type: int |
| ProcessID | Process ID of the SQL Server service. (ProcessID is useful in identifying which sqlservr.exe belongs to this instance.)
Returns NULL if invalid input or error. Base data type: int |
| ProductVersion | The version of the instance of SQL Server, in the form of 'major.minor.build'.
Base data type: varchar(128) |
| ProductLevel | The level of the version of the SQL Server instance.
Returns: Base data type: nvarchar(128). |
| ServerName | Both the Windows NT server and instance information associated with a specified instance of SQL Server.
Returns NULL if invalid input or error. Base data type: nvarchar |
Return Types
sql_variant
Remarks
The ServerName property of the SERVERPROPERTY function and @@SERVERNAME return similar information. The ServerName property provides the Windows NT server and instance name that together make up the unique server instance. @@SERVERNAME provides the currently configured local server name.
ServerName property and @@SERVERNAME return the same information if the default server name at the time of installation has not been changed. The local server name can be configured by executing sp_addserver and sp_dropserver.
If the local server name has been changed from the default server name at install time, then @@SERVERNAME returns the new name.
Examples
This example used the SERVERPROPERTY function in a SELECT statement to return information about the current server. This scenario is useful when there are multiple instances of SQL Server installed on a Windows NT server, and the client needs to open another connection to the same instance used by the current connection.
SELECT CONVERT(char(20), SERVERPROPERTY('servername'))