sp_datatype_info

Description

Returns information about a particular datatype or about all supported datatypes.

Syntax

sp_datatype_info [data_type]

Parameters

data_type: is the ODBC code number for the specified datatype about which sp_datatype_info returns information. See Table 11-5 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 function corresponds to the ODBC function SQLGetTypeInfo.

Results

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

DATA_TYPE
TYPE_NAME

The DatatypeInfo property specifies whether information is returned about T-SQL datatypes or target database datatypes. For configuration information, see “DatatypeInfo”.

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

Table 11-8 shows the result set.

**Table 11-8: 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 indicates that the column does not accept NULL values. 1 indicates that the column accepts NULL values.
CASE_SENSITIVE	smallint NOT NULL	Indicates whether the datatype distinguishes between uppercase and lowercase characters: 0 indicates that the datatype is not a character type or is not case sensitive. 1 indicates that the datatype is a character type and is case sensitive.
SEARCHABLE	smallint NOT NULL	Indicates how this datatype is used in where clauses: 0 indicates that the datatype cannot be used in a where clause. 1 indicates that the datatype can be used in a where clause.
UNSIGNED_ATTRIBUTE	smallint	Indicates whether this attribute is unsigned: 0 indicates that the datatype is signed. 1 indicates that the datatype is unsigned. NULL indicates that the datatype is not numeric.
MONEY	smallint NOT NULL	Indicates whether this is a money datatype: 0 indicates that it is not a money datatype. 1 indicates that it is a money datatype.
AUTO_INCREMENT	smallint	Indicates whether this datatype automatically increments: 0 indicates that columns of this datatype do not automatically increment. 1 indicates that columns of this datatype automatically increment. NULL indicates that 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.