Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » System FunctionsRSS Feeds

SERVERPROPERTY Function

Posted By: Sarita Patel     Category: SQL Server     Views: 3889

This article explains about SERVERPROPERTY function in sql server with examples.

SERVERPROPERTY function returns property information about the server instance. 


Syntax of SERVERPROPERTY Function :

SERVERPROPERTY (propertyname)

propertyname is an expression containing the property information to be returned for the server. It can be one of below values.

Property Name

Returned Values

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 (MSDE 2000)
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 type of SERVERPROPERTY function is a sql_variant.




Examples of SERVERPROPERTY Function :

Example 1 : Use of SERVERPROPERTY function in select clause 

SELECT SERVERPROPERTY('Edition')

Output
Enterprise Evaluation Edition

Above example returns edition of sql server installed in system.
  
Share: 

 
 

Didn't find what you were looking for? Find more on SERVERPROPERTY Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of SERVERPROPERTY Function is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!