SP_SERVER_INFO
Introduction
The SP_SERVER_INFO is a system stored procedure which Returns a list of attribute names and matching values for SQL Server.
Syntax
sp_server_info [[@attribute_id = ] 'attribute_id']
Arguments
@attribute_id =
It is the INTEGER ID of attributes. Default is NULL.
Attributes Details
ATTRIBUTE_ID
|
ATTRIBUTE_NAME Description
|
ATTRIBUTE_VALUE
|
1
|
DBMS_NAME
|
SQL Server
|
2
|
DBMS_VER
|
SQL Server 2012 - 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.
|
Y
|
109
|
INCREASE_COLUMN_LENGTH
Specifies whether column size can be increased.
|
Y
|
110
|
DDL_IN_TRANSACTION
Specifies whether DDL statements can appear in transactions.
|
Y
|
111
|
DESCENDING_INDEXES
Specifies whether descending indexes are supported.
|
Y
|
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
|
Example of SP_SERVER_INFO
EXEC sp_server_info @attribute_id=1
attribute_id attribute_name attribute_value
1 DBMS_NAME Microsoft SQL Server
If we look at carefully of Attribute ID 12, 13, 14 and 15 we can find the Maximum Table Length, Max Column Length etc.
References
