sp_datatype_info

Description

Returns information about a particular datatype or all supported datatypes.

Syntax

sp_datatype_info [data_type]

Parameters

data_type: is the ODBC code number for the specified datatype for which sp_datatype_info returns information. See Table 11-1 for a description of these codes.

Usage

The data_type parameter specifies the ODBC datatype for which information is requested. If this parameter is not provided, sp_datatype_info returns information about all supported datatypes.
This procedure corresponds to the ODBC function SQLGetTypeInfo.
The DatatypeInfo property specifies whether information is returned for Transact SQL datatypes or target database datatypes. For configuration information, see “DatatypeInfo”. If the value for data_type equals:
- target, the sp_datatype_info returns all target datatypes and their associated ODBC datatypes. A specific ODBC datatype may be used to represent multiple target datatypes.
- transact, the sp_datatype_info returns the T-SQL datatype that best matches each ODBC datatype that the target represents.

Results

sp_datatype_info returns a list of datatypes with information about each. Results are ordered by these columns:

DATA_TYPE
TYPE_NAME

The lengths for varchar columns shown in the result set tables are maximums; the actual lengths depend on the target database.

Table 11-3 shows the result set.

Table 11-3: Result set for **sp_datatype_info**
Column	Datatype	Description
TYPE_NAME	varchar(128) NOT NULL	Name of the T-SQL datatype or the target database datatype that corresponds to the ODBC datatype in the DATA_TYPE column.
DATA_TYPE	smallint NOT NULL	ODBC datatype to which all columns of this type are mapped.
PRECISION	int	Maximum precision allowed for this datatype; NULL is returned for datatypes where precision is not applicable.
LITERAL_PREFIX	varchar(128)	Characters used to prefix a literal; NULL is returned for datatypes where a literal prefix is not applicable.
LITERAL_SUFFIX	varchar(128)	Characters used to mark the end of a literal; NULL is returned for datatypes where a literal suffix is not applicable.
CREATE_PARAMS	varchar(128)	Description of the creation parameters required for this datatype (for example: precision and scale); NULL is returned if the datatype does not have creation parameters.
NULLABLE	smallint NOT NULL	Indicates whether the datatype accepts NULL values: 0 means the column does not accept NULL values. 1 means the column accepts NULL values.
CASE_SENSITIVE	smallint NOT NULL	Indicates whether the datatype distinguishes between uppercase and lowercase characters: 0 means the datatype is not a character type or is not case sensitive. 1 means the datatype is a character type and is case sensitive.
SEARCHABLE	smallint NOT NULL	Indicates how this datatype is used in where clauses: 0 means the datatype cannot be used in a where clause. 1 means the datatype can be used in a where clause.
UNSIGNED_ATTRIBUTE	smallint	Indicates whether this attribute is unsigned: 0 means the datatype is signed. 1 means the datatype is unsigned. NULL means the datatype is not numeric.
MONEY	smallint NOT NULL	Indicates whether this is a money datatype: 0 means it is not a money datatype. 1 means it is a money datatype.
AUTO_INCREMENT	smallint	Indicates whether this datatype automatically increments: 0 means columns of this datatype do not automatically increment. 1 means columns of this datatype automatically increment. NULL means the column is not numeric and does not have a sign.
LOCAL_TYPE_NAME	varchar(128)	The database name or the T-SQL name for the datatype.
MINIMUM_SCALE	smallint	Minimum scale for the datatype; NULL if scale is not applicable.
MAXIMUM_SCALE	smallint	Maximum scale for the datatype; NULL if scale is not applicable.