Require Information About Your SQL Server Instance?

Once you are responsible for an instance of SQL Server, whether it is an Enterprise edition or an Express edition, you should know some basic information about the instance.  There are various reasons why this information may be necessary so it is important that you know how and where to find it.

Some of the basic information such as the instance name, SQL Server edition, SQL Server version, etc. can be found on the General Page of the Server Properties dialog box.

To access this dialog box, perform the following steps:

1. Start SQL Server Management Studio (SSMS).
2. Connect to your instance using the necessary Authentication.
3. Right-click the server name in the Object Explorer View and select 'Properties' (see diagram below).

  

The Server properties dialog box has many pages, but in this post the main focus is the General Page (see diagram below).

Some of the instance information specified in the dialog box can also be returned by the SERVERPROPERTY function.

The syntax is:

 SERVERPROPERTY ( propertyname )

Where propertyname is an expression that contains the property information that is returned.

You can get some basic information specified in the Server Properties dialog box by using the following values as propertyname:

1. InstanceName - Returns name of the instance that you are connected to. (Null if the instance name is the default instance) .
2. ProductVersion - Specifies the version of the instance of SQL Server.
3. Edition - Returns the product edition of the instance of SQL Server.

There is some information returned by the function that does not exist in the dialog box, such as:

1. ProductLevel - Specifies the level of the version of the instance of SQL Server.
2. ResourceLastUpdateDateTime - Returns the date and time that the Resource database was last updated.
3. ComputerNamePhysicalNetBIOS - Returns the NetBIOS name of the local computer on which the instance of SQL Server is currently running.

Results from MSDN SERVERPROPERTY query

For a list of the other values for propertyname and a description of the returned values check here (Note: This link is for SQL Server 2008 R2).