For SQL Server 6.5 information, see sp_datatype_info in What's New for SQL Server 6.5.
Returns information about the datatypes supported by the current environment.
sp_datatype_info [data_type]
where
The sp_datatype_info catalog stored procedure is equivalent to SQLGetTypeInfo in ODBC. The results returned are ordered by DATA_TYPE and then by how closely the datatype maps to the corresponding ODBC SQL data type.
This is the results set:
| Column | Datatype | Description |
|---|---|---|
| TYPE_NAME | varchar(30) | Is a DBMS-dependent datatype name (the same as the TYPE_NAME column in the sp_columns results set). |
| DATA_TYPE | smallint | Is a code for the ODBC type to which all columns of this type are mapped. |
| PRECISION | int | Is the maximum precision of the datatype on the data source. NULL is returned for datatypes where precision is not applicable. |
| LITERAL_PREFIX | varchar(32) | Are character(s) used before a literal. For example, a single quotation mark (') for character types and 0x for binary in SQL Server. |
| LITERAL_SUFFIX | varchar(32) | Are character(s) used to terminate a literal. For example, a single quotation mark (') for character types and none for binary. |
| CREATE_PARAMS | varchar(32) | Is a description of the creation parameters for this datatype. For example, decimal is "precision, scale", float is NULL, and varchar is "max_length". |
| NULLABLE | smallint | Specifies nullability. 1 means this datatype can be created allowing null values; 0 means null values are not allowed. |
| CASE_SENSITIVE | smallint | Specifies case sensitivity. 1 means all columns of this type are case-sensitive (for collations); 0 means they are case-insensitive. |
| SEARCHABLE | smallint | Is a column type. 1 means columns of this type can be used in a WHERE clause. |
| UNSIGNED_ATTRIBUTE | smallint | Specifies the sign of the datatype. 1 means the datatype is unsigned; 0 means the datatype is signed. |
| MONEY | smallint | Specifies the money datatype. 1 means it is a money datatype; 0 means it is not. |
| AUTO_INCREMENT | smallint | Specifies auto-incrementing. 1 means the datatype is auto-incrementing; 0 means the datatype is not. NULL is returned if the attribute is not applicable to the datatype. An application can insert values into a column that has this attribute, but it cannot update the values in the column. This attribute is valid only for numeric datatypes. |
| LOCAL_TYPE_NAME | varchar(128) | Is the localized version of the data source-dependent name of the datatype. For example, DECIMAL is DECIMALE in French. NULL is returned if a localized name is not supported by the data source. |
| MINIMUM_SCALE | smallint | Is the minimum scale of the datatype on the data source. If a datatype has a fixed scale, the MINIMUM_SCALE and MAXIMUM_SCALE columns both contain this value. NULL is returned where scale is not applicable. For more information, see the Datatypes topic. |
| MAXIMUM_SCALE | smallint | Is the maximum scale of the datatype on the data source. If the maximum scale is not defined separately on the data source, but is instead defined to be the same as the maximum precision, this column contains the same value as the PRECISION column. For more information, see the Datatypes topic. |
| USERTYPE | smallint | The identifier for the datatype. In SQL Server 6.0, this is the uid of the datatype from the syscomments table. |