sp_server_info (Transact-SQL)

 

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

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

  
sp_server_info [[@attribute_id = ] 'attribute_id']  

[ @attribute_id = ] 'attribute_id'
Is the integer ID of the attribute. attribute_id is int, with a default of NULL.

None

Column nameData typeDescription
ATTRIBUTE_IDintID number of the attribute.
ATTRIBUTE_NAMEvarchar(60)Attribute name.
ATTRIBUTE_VALUEvarchar(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_IDATTRIBUTE_NAME DescriptionATTRIBUTE_VALUE
1DBMS_NAMESQL Server
2DBMS_VERSQL Server 2016 - x.xx.xxxx
10OWNER_TERMowner
11TABLE_TERMtable
12MAX_OWNER_NAME_LENGTH128
13TABLE_LENGTH

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

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

Specifies the maximum number of characters for a column name.
128
16IDENTIFIER_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
17TX_ISOLATION

Specifies the initial transaction isolation level the server assumes, which corresponds to an isolation level defined in SQL-92.
2
18COLLATION_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
19SAVEPOINT_SUPPORT

Specifies whether the underlying DBMS supports named savepoints.
Y
20MULTI_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
22ACCESSIBLE_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
100USERID_LENGTH

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

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

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

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

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

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

Specifies whether tables can be renamed.
Y
107RENAME_COLUMN

Specifies whether columns can be renamed.
Y
108DROP_COLUMN

Specifies whether columns can be dropped.
Y
109INCREASE_COLUMN_LENGTH

Specifies whether column size can be increased.
Y
110DDL_IN_TRANSACTION

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

Specifies whether descending indexes are supported.
Y
112SP_RENAME

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

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

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

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

Requires SELECT permission on the schema.

Catalog Stored Procedures (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions

ADD
Show: