Basic Logics · Data Analysis · Play in SQL · Real Scenario Logic's

How to Know the Datatype and Properties of a Variable and Columns of a Table

Introduction

In this blog post, we will see How to find the Datatype and Properties of a Variable and Columns of a Table. The Easy method for this Question is, using SQL_VARIANT_PROPERTY Function.

SQL_VARIANT_PROPERTY

The SQL_VARIANT_PROPERTY Function returns the base datatype and other basic information like Precision, Scale, Total bytes, Collation and Maximum Length.

SYNTAX

SQL_VARIANT_PROPERTY ( expression , property )

Where

  • Expression is a single data value that can be a single constant, variable, column, or scalar function (sql_variant here)
  • Property needs to be, any of the name of the sql_variant property ( BaseType, Precision, Scale, TotalBytes, Collation and MaxLength)
sql_variant_property with values

To Know the Datatype and Properties of a Variable

SQL Script

DECLARE @sample VARCHAR(10),@sample1 DECIMAL(5,2),@sample2 sql_variant;  
SET @sample = 'ARUL'; SET @sample1 = 77.56; SET @sample2 = 1;
SELECT @sample AS 'INPUT',  
SQL_VARIANT_PROPERTY(@sample, 'BaseType') AS 'BaseType',
SQL_VARIANT_PROPERTY(@sample, 'Precision') AS 'Precision',
SQL_VARIANT_PROPERTY(@sample, 'Scale') AS 'Scale',
SQL_VARIANT_PROPERTY(@sample, 'TotalBytes') AS 'TotalBytes',
SQL_VARIANT_PROPERTY(@sample, 'MaxLength') AS 'MaxLength',
SQL_VARIANT_PROPERTY(@sample, 'Collation') AS 'Collation';
SELECT @sample1 AS 'INPUT',  
SQL_VARIANT_PROPERTY(@sample1, 'BaseType') AS 'BaseType',
SQL_VARIANT_PROPERTY(@sample1, 'Precision') AS 'Precision',
SQL_VARIANT_PROPERTY(@sample1, 'Scale') AS 'Scale',
SQL_VARIANT_PROPERTY(@sample1, 'TotalBytes') AS 'TotalBytes',
SQL_VARIANT_PROPERTY(@sample1, 'MaxLength') AS 'MaxLength',
SQL_VARIANT_PROPERTY(@sample1, 'Collation') AS 'Collation';
SELECT @sample2 AS 'INPUT',  
SQL_VARIANT_PROPERTY(@sample2, 'BaseType') AS 'BaseType',
SQL_VARIANT_PROPERTY(@sample2, 'Precision') AS 'Precision',
SQL_VARIANT_PROPERTY(@sample2, 'Scale') AS 'Scale',
SQL_VARIANT_PROPERTY(@sample2, 'TotalBytes') AS 'TotalBytes',
SQL_VARIANT_PROPERTY(@sample2, 'MaxLength') AS 'MaxLength',
SQL_VARIANT_PROPERTY(@sample2, 'Collation') AS 'Collation';

Image Pic

Result Set

To Know the Datatype and Properties of a Column of a Table

SQL Script

IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL
	DROP TABLE #TestTable;
GO
CREATE TABLE #TestTable(colA int, colB decimal(5,2)); 
GO
INSERT INTO #TestTable VALUES (1,75.52);
GO  
SELECT   
SQL_VARIANT_PROPERTY(colB, 'BaseType') AS 'BaseType',
SQL_VARIANT_PROPERTY(colB, 'Precision') AS 'Precision',
SQL_VARIANT_PROPERTY(colB, 'Scale') AS 'Scale',
SQL_VARIANT_PROPERTY(colB, 'TotalBytes') AS 'TotalBytes',
SQL_VARIANT_PROPERTY(colB, 'MaxLength') AS 'MaxLength',
SQL_VARIANT_PROPERTY(colB, 'Collation') AS 'Collation'
FROM #TestTable WHERE colA=1;

Image Pic

Result Set

Conclusion

Thus, we saw How to find the Datatype and Properties of a Variable and Columns of a Table using SQL_VARIANT_PROPERTY Function. The above scripts and concept would work in both Azure SQL DB and On-Premises. Unit Tested as well.

Thanks for reading and Follow Blog and Show your Support for many more interesting upcoming Posts!

Advertisement

One thought on “How to Know the Datatype and Properties of a Variable and Columns of a Table

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 )

Connecting to %s