sp_server_info (Transact-SQL)

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. Used in ODBC only.

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 Specifies the owner name (database principal in SQL Server) or schema name. OWNER_TERM is used in the ODBC 2.0 API, while SQL_SCHEMA_TERM is used in the ODBC 3.0 API.	owner
11	TABLE_TERM Specifies the table object, which can be a table or view in SQL Server.	table
12	MAX_OWNER_NAME_LENGTH Specifies the maximum number of characters for the owner or schema name.	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

• Catalog stored procedures (Transact-SQL)
• System stored procedures (Transact-SQL)
• SQLGetInfo Function