sql server - retrieve all records that contain multi-byte data

What does "multi-byte" data mean?  Do you mean unicode strings?

yes. for example, any records that have chinese / japanese characters in them

Please rephrase your question..

But I guess you should take a look at LEN vs. DATALENGTH:

 

DECLARE @Sample TABLE ( Test NVARCHAR(255) );

INSERT INTO @Sample
VALUES	( N'1' ),
	( N'क्षि' );

SELECT	LEN(Test),
	DATALENGTH(Test),
	Test
FROM	@Sample;

Select allOpen in new window

SELECT O.name AS TableName, C.name AS ColumnName,
      T.name AS TypeName, C.max_length
FROM sys.objects AS O
INNER JOIN sys.columns AS C
      ON O.object_id = C.object_id
INNER JOIN sys.types AS T
      ON C.user_type_id = T.user_type_id
WHERE O.type = 'U'
      AND C.user_type_id = 231

@Brian - I'm looking for a query to tell me if certain records in my table contains any multi-byte chars, i.e. chinese chars. Most of the records contains plain english, but i have a few that contain those special chars. Your query tells me the data type for each field, which is not what I'm looking for.

@st5an - not sure how you else you want me to re-phrase the question...the query you gave me doesn't really differentiate from the two conditions I'm looking for

What conditions?? What about a concise and complete example as runnable T-SQL script... I don't like guessing that much..

in pseudo code, it would be something like:

select * from table where field contains any multi-byte char

the field in question would contain something like

シュアビード レデュースドキャビティ（RC） アプリケーター

i get a zero returned from the function when I run the query (after creating the function).

SELECT Cache_Obj_Filename, dbo.HasDoubleByte(Cache_Obj_Filename) FROM Data where .......

the record shows up, but so do all of the other records

The function was updated in the meantime...