sql server - T-SQL: Is the variable NVARCHAR(MAX)? -

how can actual type of column or variable in t-sql?

i know sql_variant_property, fails miserably nvarchar(max):

declare @foo1 nvarchar(10) = n'a' select sql_variant_property(@foo1, 'basetype') --works fine declare @foo2 nvarchar(max) = n'a' select sql_variant_property(@foo2, 'basetype') --fails error: --operand type clash: nvarchar(max) incompatible sql_variant 

is there else able tell me if variable contains value of type nvarchar(max)?

some background:

i working on procedure should reorder columns of table: rename old table, create new one, copy data , drop old one. in order this, indexes, views, constraints, etc. need recreated on new table.

i want make sure nothing gets lost in automatic process. that, copy values relevant system-tables generic temp-table , compare values after reordering. works fine now, fails when trying detect type of nvarchar(max)-columns.

andreas,

if looking discover data type , length of column use following code. note -1 used (max) stated in schema. add clause in specify table or column name

select      tb.name tablename, cl.name columnname             , cl.system_type_id, ty.name, cl.max_length        sys.columns cl inner join  sys.tables tb on tb.object_id = cl.object_id inner join  sys.types ty on cl.system_type_id = ty.system_type_id order    cl.max_length