Share via


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.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_server_info [[@attribute_id = ] 'attribute_id']

Arguments

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

Return Code Values

None

Result Sets

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 2005 - 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.

Returns Y, if SQL Server 2000 or later is running and N, for earlier releases.

109

INCREASE_COLUMN_LENGTH

Specifies whether column size can be increased.

Returns Y, if SQL Server 2000 or later is running and N, for earlier releases.

110

DDL_IN_TRANSACTION

Specifies whether DDL statements can appear in transactions.

Returns Y, if SQL Server version 6.5 or later is running and N, for earlier releases.

111

DESCENDING_INDEXES

Specifies whether descending indexes are supported.

Returns Y, if SQL Server 2000 or later is running and N, for earlier releases.

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 SELECT permission on the schema.