SQL SERVER – How to Identify Datatypes and Properties of Variable

Just another day I received very interesting question in email from Zovis and we exchanged few emails. I am converting our conversation in the chat format to understand it easily.

Zovis: How do I identify the datatype and other properties of the variable?

Pinal: Just check the datatype of the declared variable. Isn’t that easy?

Zovis: Yes, that is true but we have absolutely different need. We do not know what is the datatype of the variable. We need to check that with the help of code.

Pinal: Can you give me quick example?

Zovis: Sure. If our variable is numeric then we want to know the precision and scale, but if our datatype is varchar we want to know the max length and collation. We want to do all this with T-SQL. Can you help?

Pinal: Hmm… well you can do it with the help of SQL_VARIANT_PROPERTY. Let me show you example.

Let us see two examples -

In the first case our variable is integer.
DECLARE @myVar INT
SET
@myVar = 0
SELECT SQL_VARIANT_PROPERTY(@myVar,'BaseType') BaseType,
SQL_VARIANT_PROPERTY(@myVar,'Precision') Precisions,
SQL_VARIANT_PROPERTY(@myVar,'Scale') Scale,
SQL_VARIANT_PROPERTY(@myVar,'TotalBytes') TotalBytes,
SQL_VARIANT_PROPERTY(@myVar,'Collation') Collation,
SQL_VARIANT_PROPERTY(@myVar,'MaxLength') MaxLengths

In the second case our variable is varchar (100).
DECLARE @myVar VARCHAR(100)
SET @myVar = 0
SELECT SQL_VARIANT_PROPERTY(@myVar,'BaseType') BaseType,
SQL_VARIANT_PROPERTY(@myVar,'Precision') Precisions,
SQL_VARIANT_PROPERTY(@myVar,'Scale') Scale,
SQL_VARIANT_PROPERTY(@myVar,'TotalBytes') TotalBytes,
SQL_VARIANT_PROPERTY(@myVar,'Collation') Collation,
SQL_VARIANT_PROPERTY(@myVar,'MaxLength') MaxLengths

What do you think of this function? Have you ever used in your production environment?

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

About these ads

4 thoughts on “SQL SERVER – How to Identify Datatypes and Properties of Variable

  1. Hi Pinal,

    I check your web site every single day and it’s awesome. Thank you for the nice work

    What will happen if you variable is varchar(max)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s