Search notes:

SQL Server function: type_id

type_id(typename) returns a data type's id given its name.

select
   tp.name       type_name,
   tp.xusertype,
   type_id(tp.name)
from
   sys.systypes tp