To Know Datatype and Properties for Variables and Table Columns

Written by

Introduction

How to find the Datatype and Properties of a Variable and Columns of a Table? The Simple and Easy Answer 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 PrecisionScaleTotal bytesCollation 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 ( BaseTypePrecisionScaleTotalBytesCollation 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!

Introduction How to find the Datatype and Properties of a Variable and Columns of a Table? The Simple and Easy Answer 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!

User Rating: Be the first one !
0
Article Categories:
SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

Shares