sp_server_info (T-SQL)

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 7.00 - 7.00.xxx (Intel X86) Oct 17 1997 02:53:32 Copyright (c) 1988-1997 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).	MIXED or SENSITIVE, depending upon whether SQL Server is set to case-sensitive or case-insensitive.
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 ID
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.	30
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.	N
109	INCREASE_COLUMN_LENGTH Specifies whether column size can be increased.	N
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.	N
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.