SELECT [type],CASE [type] 
              WHEN 'TT' THEN 'TYPE_TABLE'
              WHEN 'FN' THEN 'SQL_SCALAR_FUNCTION'
              WHEN 'C' THEN 'CHECK_CONSTRAINT'
              WHEN 'SQ' THEN 'SERVICE_QUEUE' 
              WHEN 'F' THEN 'FOREIGN_KEY_CONSTRAINT' 
              WHEN 'U' THEN 'USER_TABLE' 
              WHEN 'D' THEN 'DEFAULT_CONSTRAINT' 
              WHEN 'PK' THEN 'PRIMARY_KEY_CONSTRAINT'
              WHEN 'S' THEN 'SYSTEM_TABLE' 
              WHEN 'IT' THEN 'INTERNAL_TABLE' 
              WHEN 'P' THEN 'SQL_STORED_PROCEDURE' 
              WHEN 'TF' THEN 'SQL_TABLE_VALUED_FUNCTION' 
              WHEN 'TR' THEN 'SQL_TRIGGER' 
              END AS [type description]
FROM (
    SELECT DISTINCT [type] FROM sys.objects
) O