Results

sp_datatype_info returns a list of datatypes with information about each column.

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.

Result Set for sp_datatype_info

Column

Datatype

Description

TYPE_NAME

varchar(128)

NOT NULL

Name of the Transact-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 Transact-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.
Parent topic: sp_datatype_info