declare @variants table ( Value sql_variant, ordinal int identity(1,1) primary key clustered )
insert into @variants ( Value ) select convert( bigint, 4 )
insert into @variants ( Value ) select convert( binary(10), '1234567890' )
insert into @variants ( Value ) select convert( char(10), '1234567890' )
insert into @variants ( Value ) select convert( date, '2012-03-21 01:23:45' )
insert into @variants ( Value ) select convert( datetime, '2012-03-21 01:23:45' )
insert into @variants ( Value ) select convert( datetime2(6), '2012-03-21 01:23:45' )
insert into @variants ( Value ) select convert( datetimeoffset, '2012-03-21 01:23:45' )
insert into @variants ( Value ) select convert( decimal(9,3), 3.45 )
insert into @variants ( Value ) select convert( float, 0.12 )
insert into @variants ( Value ) select convert( int, 0 )
insert into @variants ( Value ) select convert( money, 7.89 )
insert into @variants ( Value ) select convert( nchar(10), '1234567890' )
insert into @variants ( Value ) select convert( numeric, 4.56 )
insert into @variants ( Value ) select convert( nvarchar(10), '1234567890' )
insert into @variants ( Value ) select convert( real, 3 )
insert into @variants ( Value ) select convert( smalldatetime, '2012-03-21 01:23:45' )
insert into @variants ( Value ) select convert( smallint, 2 )
insert into @variants ( Value ) select convert( smallmoney, 0.12 )
insert into @variants ( Value ) select convert( time, '01:23:45' )
insert into @variants ( Value ) select convert( tinyint, 1 )
insert into @variants ( Value ) select convert( uniqueidentifier, newid() )
insert into @variants ( Value ) select convert( varbinary(10), '1234567890' )
insert into @variants ( Value ) select convert( varchar(10), 'varchar(10)' )
select
    ordinal
    --,Value
    ,convert( sysname, SQL_VARIANT_PROPERTY( Value, 'BaseType' ) )
    ,convert( int, SQL_VARIANT_PROPERTY( Value, 'Precision' ) )
    ,convert( int, SQL_VARIANT_PROPERTY( Value, 'Scale' ) )
    ,convert( int, SQL_VARIANT_PROPERTY( Value, 'TotalBytes' ) )
    ,convert( sysname, SQL_VARIANT_PROPERTY( Value, 'Collation' ) )
    ,convert( int, SQL_VARIANT_PROPERTY( Value, 'MaxLength' ) )
    ,data_definition =
        case
            when SQL_VARIANT_PROPERTY(Value,'BaseType') in ('bit', 'tinyint', 'smallint', 'int', 'bigint', 'real', 'smallmoney', 'money','text', 'ntext', 'image', 'date', 'time', 'smalldatetime', 'datetime','timestamp', 'sql_variant', 'xml', 'hierarchyid', 'uniqueidentifier')
                then convert( nvarchar, SQL_VARIANT_PROPERTY( Value, 'BaseType' ) )
            when SQL_VARIANT_PROPERTY(Value,'BaseType') in ('numeric', 'decimal')
                then convert( nvarchar, SQL_VARIANT_PROPERTY( Value, 'BaseType' ) )
                    + N'(' + convert(nvarchar,SQL_VARIANT_PROPERTY(Value,'Precision'))
                    + N',' + convert(nvarchar,SQL_VARIANT_PROPERTY(Value,'Scale'))
                    + N')'
            when SQL_VARIANT_PROPERTY(Value,'BaseType') in ('float')
                then convert( nvarchar, SQL_VARIANT_PROPERTY( Value, 'BaseType' ) )
                    + N'(' + convert(nvarchar,SQL_VARIANT_PROPERTY(Value,'Precision'))
                    + N')'
            when SQL_VARIANT_PROPERTY(Value,'BaseType') in ('char', 'varchar', 'binary', 'varbinary')
                then convert( nvarchar, SQL_VARIANT_PROPERTY( Value, 'BaseType' ) )
                    + N'('
                    + case
                        when convert( int, SQL_VARIANT_PROPERTY(Value,'MaxLength') ) = -1
                            then 'max'
                        else convert(nvarchar,SQL_VARIANT_PROPERTY(Value,'MaxLength'))
                        end
                    + N')'
            when SQL_VARIANT_PROPERTY(Value,'BaseType') in ('nchar', 'nvarchar')
                then convert( nvarchar, SQL_VARIANT_PROPERTY( Value, 'BaseType' ) )
                    + N'('
                    + case
                        when convert( int, SQL_VARIANT_PROPERTY(Value,'MaxLength') ) = -1
                            then 'max'
                        else convert(nvarchar,convert(int,SQL_VARIANT_PROPERTY(Value,'MaxLength'))/2)
                        end
                    + N')'
            when SQL_VARIANT_PROPERTY(Value,'BaseType') in ('datetime2', 'datetimeoffset')
                then convert( nvarchar, SQL_VARIANT_PROPERTY( Value, 'BaseType' ) )
                    + case
                        when convert( int, SQL_VARIANT_PROPERTY(Value,'Scale') ) > 0
                            then N'(' + convert(nvarchar,SQL_VARIANT_PROPERTY(Value,'Scale')) + N')'
                        else N'' end
                else 'unknown'
            end
    from @variants
    order by ordinal