SQL SERVER – Stored Procedure sp_datatype_info to Get Supported Data Types

I get to work with various different versions and environment when I am busy with my clients with Comprehensive Database Performance Health Check. While I work with different customers often I forget what data type is supported by which version of SQL Server. Here is a small quick trick, I use all the time when I need to know what are the different data types supported by the version I am working with.

USE master;
GO
EXEC sp_datatype_info
GO 

When you run the above script it gives us a very informative output like what kind of datatype it supports, precision, nullability, searchability, scale as well as a prefix to the datatype.

For example, look at the column Searchable. you will see different values there in that column. Here is what it means:

Specifies the search capability of the column type:

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

Well, there are many interesting columns as well. Here is another example column Case Sensitivity. You can see that XML column is case sensitive column.

I really think this small neat script can be very handy when you are busy doing SQL Server Performance Tuning Engagement. If you know such learning nugget, please share with me and I will publish it with due credit to you.

Reference: Pinal Dave (https://blog.sqlauthority.com)