sp_server_info (Transact-SQL)
Returns a list of attribute names and matching values for SQL Server 2005, the database gateway, or the underlying data source.
.gif "Topic link icon")
Transact-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.
See Also
Reference
Catalog Stored Procedures (Transact-SQL)
System Stored Procedures (Transact-SQL)