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_nameThis 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