SQL_VARIANT_PROPERTY function

If you want to know the datatype or length of a column, you can query on the system table syscolumns or
system
view INFORMATION_SCHEMA.COLUMNS

But you dont need to query on system objects if you use SQL_VARIANT_PROPERTY function

Example

SELECT
    TOP 1
        SQL_VARIANT_PROPERTY('column_name','basetype')
FROM
    table_name

which gives you the datatype of the column_name

This is an example from Northwind..Orders table

SELECT 
    TOP 1
        SQL_VARIANT_PROPERTY('orderid','basetype') as basetype,
        SQL_VARIANT_PROPERTY('orderid','maxlength') as maxlength,
        SQL_VARIANT_PROPERTY('orderid','precision') as precision,
        SQL_VARIANT_PROPERTY('orderid','scale') as scale,
        SQL_VARIANT_PROPERTY('orderid','collation') as collation
FROM
        Northwind..Orders

Published Saturday, November 1, 2008 9:08 AM by Madhivanan

Comments

No Comments