How do I find the data types such as xml or text or image of a specified table?
USE <database_name>;
GO
SELECT name AS column_name
,column_id
,TYPE_NAME(user_type_id) AS type_name
,max_length
,CASE
WHEN max_length = -1 AND TYPE_NAME(user_type_id) <> 'xml'
THEN 1
ELSE 0
END AS [(max)]
FROM sys.columns
WHERE object_id=OBJECT_ID('<schema_name.table_name>')
AND ( TYPE_NAME(user_type_id) IN ('xml','text', 'ntext','image')
OR (TYPE_NAME(user_type_id) IN ('varchar','nvarchar','varbinary')
AND max_length = -1)
);
GO
(extracted from BOL)