Export (0) Print
Expand All

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.

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 Description

ATTRIBUTE_VALUE

1

DBMS_NAME

SQL Server

2

DBMS_VER

SQL Server 2014 - x.xx.xxxx

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

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

Requires SELECT permission on the schema.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft