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

SQL SERVER - How to Identify Datatypes and Properties of Variable intvariant

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 (https://blog.sqlauthority.com)

Previous Post
Personal Technology – How to Dress for an Interview – Guidelines and Suggestions
Next Post
SQL SERVER – Monitor Database via a Heatmap, Alarms List and Realtime Diagnostics for FREE

Related Posts

No results found.

8 Comments. Leave new

Leave a ReplyCancel reply

Exit mobile version