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.

SQL SERVER - Stored Procedure sp_datatype_info to Get Supported Data Types datypesearchable

USE master;
EXEC sp_datatype_info

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)

Data Type, SQL Scripts, SQL Server
Previous Post
SQL SERVER – T-SQL Script to List Automatic Seeding of Always On Availability Group
Next Post
SQL SERVER – Download and Install SQL Server Sample Databases (Updated Post for 2019)

Related Posts

1 Comment. Leave new

  • manishtechsite
    May 16, 2019 10:08 am

    i got 0 in searchable column for image & xml datatype
    what does it mean ?


Leave a Reply