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