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

http://technet.microsoft.com/en-us/library/ms176094.aspx

Hope you like it.

Posted by: MR. JOYDEEP DAS