sp_server_info

Transact-SQL Reference

Transact-SQL Reference

sp_server_info

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

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.

These are the attributes. Microsoft DB-Library and 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 Microsoft SQL Server
2 DBMS_VER Microsoft SQL Server 2000 - 8.00.xxx (Intel X86)
May 31 2000 00:54:06 Copyright (c) 1988-2000 Microsoft Corporation
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 is running and N, for earlier releases.
109 INCREASE_COLUMN_LENGTH

Specifies whether column size can be increased.

Returns Y, if SQL Server 2000 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 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

Execute permissions default to the public role.

See Also

System Stored Procedures