0

Currently, I have a function to get list of columns of 1 table with detail attribute. And off course, there are some tables with a lot of columns. So, the output will be over 10.000 characters.

Here I test like this:

declare @aa nvarchar(max) 
set @aa = dbo.fnGetColumnList('Table_Name')
print @aa

The result always has around 4000 characters. It looks like the SQL has truncated it.

What I know that when we declare nvarchar(max), SQL will supports up to 2^32-1 (2GB) for this string. But why it just has around 4000 characters?

When I execute like this:

select dbo.fnGetColumnList('Table_Name')

the result is correct.

And here is the code for the function:

-- get column list from table Mapping
ALTER FUNCTION [dbo].[fnGetColumnList] ( @tblName varchar (30))
RETURNS nvarchar(max)
AS
BEGIN

Declare @sql    nvarchar(max)

set @sql = ''
SELECT @sql = @sql + case 
when CHARINDEX('char', LOWER([DBType])) > 0 then ', ['+[DBColumn]+']' + ' ['+[DBType]+']' + ' ('+convert(varchar(10),[Length])+')  NULL' + CHAR(13)
when CHARINDEX('char', LOWER([DBType])) > 0 then ', ['+[DBColumn]+']' + ' ['+[DBType]+']' + ' NULL' + CHAR(13)
ELSE ', ['+[DBColumn]+']' + ' ['+[DBType]+']' + ' NULL' + CHAR(13)
end FROM dbo.Mapping WHERE [DBTable] = @tblName

return @sql
END

Please advance.

Lang thang
  • 281
  • 1
  • 2
  • 11

3 Answers3

4

This is almost always a variable assignment type problem, as explained in:

For Nvarchar(Max) I am only getting 4000 characters in TSQL?

If it's not that, then it's probably just the settings of Print to display too few characters:

nvarchar(max) still being truncated

Having looked at the updated code, it seems like it's the second issue, your print is truncating as it's not set to show enough characters.

You should see this by running

SELECT LEN(@aa)

You'll get a number larger than 4000, showing the value is held correctly in the variable.

Community
  • 1
  • 1
James Osborn
  • 1,275
  • 7
  • 12
1

As explained in Microsoft's nvar and nvarchar docs:

A common misconception is to think that with nchar(n) and nvarchar(n), the n defines the number of characters. However, in nchar(n) and nvarchar(n), the n defines the string length in byte-pairs (0-4,000). n never defines numbers of characters that can be stored. This is similar to the definition of char(n) and varchar(n).

tdy
  • 36,675
  • 19
  • 86
  • 83
Alex
  • 11
  • 2
0

There is an option in SQL Management Studio: Tools > Options... > Query Results > SQL Server > Results to Text > Maximum number of characters displayed in each column

polybios
  • 1,159
  • 8
  • 20