SQL Server: How to Query Version and Server Information

There are three commands that can be used in SQL Server to query version information or information about the server hosting SQL Server. These commands can be run in SSMS directly, or can be used in code, for example to tailor queries for a particular database version.

The commands are xp_msver, @@Version, and sp_server_info. I have successfully used all of these commands in SQL Server 2000, SQL Server 2005, and SQL Server 2008.

Here is an example of their syntax:

EXEC xp_msver "ProductName", "ProductVersion", "Language", "Platform", "WindowsVersion", "PhysicalMemory",
"ProcessorCount" ,"FileDescription"

SELECT @@Version 'SQL Version'

EXEC sp_server_info

The output of each of these commands is different:

  1. Selecting @@Version results in a single information string of concatenated version and server hardware information.
  2. Executing xp_msver returns a table of information. The number of rows returned corresponds to the number of input parameters . If no input parameters are passed, then the full list of information is returned.
    • The returned columns are: Index, Name, Internal_Value, Character_Value
  3. Executing sp_server_info also returns a table if no parameters are passed. If you are only interested in a particular value you can pass the Id value of that value as a parameter (ie: EXEC sp_server_info 2  will return the database and version information.
    • Here are the values and their Ids:
      • 1	DBMS_NAME
        2	DBMS_VER
        10	OWNER_TERM
        11	TABLE_TERM
        12	MAX_OWNER_NAME_LENGTH
        13	TABLE_LENGTH
        14	MAX_QUAL_LENGTH
        15	COLUMN_LENGTH
        16	IDENTIFIER_CASE
        17	TX_ISOLATION
        18	COLLATION_SEQ
        19	SAVEPOINT_SUPPORT
        20	MULTI_RESULT_SETS
        22	ACCESSIBLE_TABLES
        100	USERID_LENGTH
        101	QUALIFIER_TERM
        102	NAMED_TRANSACTIONS
        103	SPROC_AS_LANGUAGE
        104	ACCESSIBLE_SPROC
        105	MAX_INDEX_COLS
        106	RENAME_TABLE
        107	RENAME_COLUMN
        108	DROP_COLUMN
        109	INCREASE_COLUMN_LENGTH
        110	DDL_IN_TRANSACTION
        111	DESCENDING_INDEXES
        112	SP_RENAME
        113	REMOTE_SPROC
        500	SYS_SPROC_VERSION

References:

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s