Sunday, December 20, 2009

SQL_VARIANT_PROPERTY() to find the info about data type?

BOL says that this function returns the base data type and other information about a sql_variant value. Have you ever looked what is datatype of GETDATE() or perhaps DB_ID() function or what is the data type of SYSTEM_USER??

DECLARE @var sql_variant
SET @var =GETDATE() --SYSTEM_USER
SELECT SQL_VARIANT_PROPERTY(@var , 'BaseType') as BaseType
, SQL_VARIANT_PROPERTY(@var , 'Precision') as Precision
, SQL_VARIANT_PROPERTY(@var , 'Scale') as Scale
, SQL_VARIANT_PROPERTY(@var , 'TotalBytes') as TotalBytes
, SQL_VARIANT_PROPERTY(@var , 'MaxLength') as MaxLength
, SQL_VARIANT_PROPERTY(@var , 'Collation') as Collation;

I found that very usefull as you can easily retun the basic info about even system objects and how SQL Server interpetes it.

1 comment:

Unknown said...

Similar to this

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/11/01/sql-variant-property-function.aspx