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:
- Selecting @@Version results in a single information string of concatenated version and server hardware information.
- 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
- 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:
Justin Cooney 