Article
10/01/2010

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