3

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

I know about SQL_VARIANT_PROPERTY, but this fails miserably 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 is able to tell me if a variable contains a value of the type NVARCHAR(MAX)?

Some background:

I am working on a procedure that should reorder the columns of a table: Rename the old table, create a new one, copy the data and drop the old one. In order to do this, all indexes, views, constraints, etc. need to be recreated on the new table.

I want to make sure that nothing gets lost in this automatic process. For that, I would like to copy most values from the relevant system-tables to a generic temp-table and compare the values after the reordering. This works perfectly fine now, but it fails when trying to detect the type of nvarchar(max)-columns.

Andreas
  • 1,997
  • 21
  • 35
  • 2
    what is the error you get? (I know what it is, but it should be a part of the question) – Kritner Jul 13 '15 at 15:50
  • related: http://stackoverflow.com/questions/7969508/operand-type-clash – Kritner Jul 13 '15 at 15:55
  • @Kritner: Added the error-message – Andreas Jul 13 '15 at 15:55
  • trying to understand when you would need this -- if it is a local variable then you define it in the code. If it is a column there are other ways to find out the type information. – Hogan Jul 13 '15 at 15:56
  • Are you trying to get the length of a VARCHAR field? – Ross Bush Jul 13 '15 at 15:57
  • @Hogan: I am dealing with the sys-tables and a string-list of column-names that should be copied to a temp-table. Unfortunately, the sys-tables do not contain meta-info about the sys-tables. – Andreas Jul 13 '15 at 15:58
  • Hopefully you understand why it is a very bad idea to reorder columns in a table? This is a requirement I would push back, it is bound to cause bugs and data integrity issues from hidden bugs. All it takes is one person using select * from this to cause problems. – HLGEM Jul 13 '15 at 18:27
  • @HLGEM: In our team, we decided to live with the few bugs ocurring now instead of living with oddly ordered columns forever. But we do also have an architecture and coding-conventions, that make it very hard to create bugs from "select *" or similar stuff. Actually, we did not have a single bug since we are doing this. – Andreas Jul 14 '15 at 14:42

2 Answers2

2

Andreas,

If you are looking to discover the data type and length of the column then you could use the following code. Note that -1 is used where (max) is stated in the schema. Add a WHERE clause in to specify table or column name

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
Tony
  • 74
  • 2
0

sql_variant can not store nvarchar(max) and Sql_variant does support strings

it will not store this types also 

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

etc...

instead of go to 

nvarchar(4000) but not nvarchar(max).
varchar(8000) but not varchar(max)
mohan111
  • 8,633
  • 4
  • 28
  • 55
  • I am willing to do that, but I need to know the type of the variable first. This is generic code that gets the value from a sys-table (sys.columns does not contain information about these columns) and should store it in a sql_variant. But in order to do the conversion for the correct columns only, I need to know the type. – Andreas Jul 13 '15 at 15:55