Get the information about Datatype in SQL Server

How to get the detailed information of datatype in SQL Server ?

sp_datatype_info is a SQL Server system stored procedure that is used to get the information about datatype which is supported by current environment.

SYNTAX

Exec sp_datatype_info

As you can see, it returns complete information about datatype in terms of Precision, nullability, case sensitivity, searchability and so on..

So, using this system stored procedure we can get useful information about any datatype and can use it in more efficient way.

Lets execute the stored procedure to see the detailed information of datatypes available in current environment.

Exec sp_datatype_info

Lets understand the output of few major columns as given below.

Nullable column returns two type of values either 1 or 0 :

1 = Allows null values.
0 = Does not allow null values.

Case_sensitive column  Specifies case sensitivity  of column the column type and returns two type of values either 1 or 0 :

1 = All columns of this type are case-sensitive.
0 = All columns of this type are case-insensitive.

Searchable column Specifies the search capability of the column type and returns possible values between range 1 to 4 :

1 = Cannot be searched.
2 = Searchable with LIKE.
3 = Searchable with WHERE.
4 = Searchable with WHERE or LIKE.

Auto_increment Specifies autoincrementing and returns three kind of values 1, 0 and null:

1 = Autoincrementing.
0 = Not autoincrementing.
NULL = Attribute not applicable.