Transact-SQL Reference

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'
'Developer Edition'
'Enterprise Edition'
'Enterprise Evaluation Edition'
'Personal Edition'
'Standard Edition'

Base data type: nvarchar(128)

Engine Edition The engine edition of the SQL Server instance installed on the server.

1 = Personal or Desktop Engine
2 = Standard
3 = Enterprise (returned for Enterprise, Enterprise Evaluation, and Developer)

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.
0 = Not Clustered.
NULL = Invalid input, or error.

Base data type: int

IsFullTextInstalled The full-text component is installed with the current instance of SQL Server.

1 = Full-text is installed.
0 = Full-text is not installed.
NULL = Invalid input, or error.

Base data type: int

IsIntegratedSecurityOnly The server is in integrated security mode.

1 = Integrated Security.
0 = Not Integrated Security.
NULL = Invalid input, or error.

Base data type: int

IsSingleUser The server is in single user mode.

1 = Single User.
0 = Not Single User
NULL = Invalid input, or error.

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.
0 = False.

Base data type: int

LicenseType Mode of this instance of SQL Server.

PER_SEAT = Per-seat mode
PER_PROCESSOR = Per-processor mode
DISABLED = Licensing is disabled.

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:
'RTM' = shipping version.
'SPn' = service pack version
'Bn', = beta version.

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'))

See Also

@@SERVERNAME