Chapter 11: Accessing Catalog Information with CSPs

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.

The following table 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)	Character(s) used to prefix a literal; NULL is returned for datatypes where a literal prefix is not applicable.
LITERAL_SUFFIX	varchar(128)	Character(s) 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.

View this book as PDF