Edit

sp_server_info (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Returns a list of attribute names and matching values for SQL Server, the database gateway, or the underlying data source.

Transact-SQL syntax conventions

Syntax

sp_server_info [ [ @attribute_id = ] attribute_id ]
[ ; ]

Arguments

[ @attribute_id = ] attribute_id

The integer ID of the attribute. @attribute_id is int, with a default of NULL.

Return code values

None.

Result set

Column name Data type Description
ATTRIBUTE_ID int ID number of the attribute.
ATTRIBUTE_NAME varchar(60) Attribute name.
ATTRIBUTE_VALUE varchar(255) Current setting of the attribute.

The following table lists the attributes. Microsoft ODBC client libraries currently use attributes 1, 2, 18, 22, and 500 at connection time.

ATTRIBUTE_ID ATTRIBUTE_NAME and description ATTRIBUTE_VALUE
1 DBMS_NAME SQL Server
2 DBMS_VER Microsoft SQL Server xxxx - x.xx.xxxx

For example, Microsoft SQL Server 2017 - 14.0.3257.3
10 OWNER_TERM owner
11 TABLE_TERM table
12 MAX_OWNER_NAME_LENGTH 128
13 TABLE_LENGTH

Specifies the maximum number of characters for a table name.		 128
14 MAX_QUAL_LENGTH

Specifies the maximum length of the name for a table qualifier (the first part of a three-part table name).		 128
15 COLUMN_LENGTH

Specifies the maximum number of characters for a column name.		 128
16 IDENTIFIER_CASE

Specifies the user-defined names (table names, column names, stored procedure names) in the database (the case of the objects in the system catalogs).		 SENSITIVE
17 TX_ISOLATION

Specifies the initial transaction isolation level the server assumes, which corresponds to an isolation level defined in SQL-92.		 2
18 COLLATION_SEQ

Specifies the ordering of the character set for this server.		 charset=iso_1 sort_order=dictionary_iso charset_num=1 sort_order_num=51
19 SAVEPOINT_SUPPORT

Specifies whether the underlying DBMS supports named savepoints.		 Y
20 MULTI_RESULT_SETS

Specifies whether the underlying database or the gateway itself supports multiple result sets (multiple statements can be sent through the gateway with multiple result sets returned to the client).		 Y
22 ACCESSIBLE_TABLES

Specifies whether in sp_tables, the gateway returns only tables, views, and so on, accessible by the current user (that is, the user who has at least SELECT permissions for the table).		 Y
100 USERID_LENGTH

Specifies the maximum number of characters for a username.		 128
101 QUALIFIER_TERM

Specifies the DBMS vendor term for a table qualifier (the first part of a three-part name).		 database
102 NAMED_TRANSACTIONS

Specifies whether the underlying DBMS supports named transactions.		 Y
103 SPROC_AS_LANGUAGE

Specifies whether stored procedures can be executed as language events.		 Y
104 ACCESSIBLE_SPROC

Specifies whether in sp_stored_procedures, the gateway returns only stored procedures that are executable by the current user.		 Y
105 MAX_INDEX_COLS

Specifies the maximum number of columns in an index for the DBMS.		 16
106 RENAME_TABLE

Specifies whether tables can be renamed.		 Y
107 RENAME_COLUMN

Specifies whether columns can be renamed.		 Y
108 DROP_COLUMN

Specifies whether columns can be dropped.		 Y
109 INCREASE_COLUMN_LENGTH

Specifies whether column size can be increased.		 Y
110 DDL_IN_TRANSACTION

Specifies whether DDL statements can appear in transactions.		 Y
111 DESCENDING_INDEXES

Specifies whether descending indexes are supported.		 Y
112 SP_RENAME

Specifies whether a stored procedure can be renamed.		 Y
113 REMOTE_SPROC

Specifies whether stored procedures can be executed through the remote stored procedure functions in DB-Library.		 Y
500 SYS_SPROC_VERSION

Specifies the version of the catalog stored procedures currently implemented.		 Current version number

Remarks

sp_server_info returns a subset of the information provided by SQLGetInfo in ODBC.

Permissions

Requires membership in the public role.

Related content