Here is a query that I have canned. It's part of a tool, and the purpose is to return information about all user-defined types. (But I realised today that it fails to return information about CLR types and table type).

The gist is fairly close to Scott's query, although he had forgotten datetimeoffset. And he had to failed to adjust the length for nchar/nvarchar. A complete query should also cover xml columns with a schema collection. I see now that my query does not handle MAX columns.

SELECT typename = CASE N.N WHEN 1 THEN lower(a.name)

WHEN 2 THEN lower(s.name) + '.' + lower(a.name)

END,

typedef = b.name +

CASE WHEN b.name IN ('nchar', 'nvarchar')

THEN '(' + ltrim(str(a.max_length / 2)) + ')'

WHEN b.name IN ('char', 'varchar', 'binary', 'varbinary')

THEN '(' + ltrim(str(a.max_length)) + ')'

WHEN b.name IN ('decimal', 'numeric')

THEN '(' + ltrim(str(a.precision)) + ',' +

ltrim(str(a.scale)) + ')'

WHEN b.name IN ('datetime2', 'time', 'datetimeoffset')

THEN '(' + ltrim(str(a.scale)) + ')'

ELSE ''

END

FROM sys.types a

JOIN sys.types b ON a.system_type_id = b.system_type_id

JOIN sys.schemas s ON a.schema_id = s.schema_id

CROSS JOIN (SELECT N = 1 UNION ALL SELECT 2) AS N

WHERE a.user_type_id <> a.system_type_id

AND b.user_type_id = b.system_type_id

ORDER BY a.name