Clever Geek Handbook

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

How can I get the actual type of a column or variable in t-sql?

I know about SQL_VARIANT_PROPERTY

, but this fails for 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 with an error:
--Operand type clash: nvarchar(max) is incompatible with sql_variant

Is there anything else that can tell me if a variable contains a value of the type NVARCHAR(MAX)

?

Some background:

I am working on a procedure that is supposed to reorder the columns of a table: rename the old table, create a new one, copy the data and drop the old one. To do this, all indexes, views, constraints, etc. Must be recreated in a new table.

I want to make sure that nothing is lost in this automated process. For this, I would like to copy most of the values ​​from the respective system tables into a common temporary table and compare the values ​​after reordering. Now this works fine, but cannot detect the type of nvarchar (max) -columns.

+3


source to share


2 answers


Andreas,

If you want to know the data type and length of a column, you can use the following code. Note that -1 is used where (max) is specified in the schema. Add a WHERE clause to specify the name of the table or column



SELECT      tb.name TableName, cl.name ColumnName
            , cl.system_type_id, ty.name, cl.max_length
FROM        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 BY    cl.max_length
+1


source


sql_variant cannot store nvarchar (max) and Sql_variant supports strings

it won't store these types either

varchar(max)
varbinary(max)
nvarchar(max)
xml
text


etc...

instead of going to

nvarchar(4000) but not nvarchar(max).
varchar(8000) but not varchar(max)
0


source






More articles:


All Articles