According to [the sysobjects documentation](https://msdn.microsoft.com/en-us/library/ms177596.aspx), `sysobjects.xtype` can be one of these object types:

```none
| xtype |              Description              |
|-------|---------------------------------------|
| AF    |  Aggregate function (CLR)             |
| C     |  CHECK constraint                     |
| D     |  Default or DEFAULT constraint        |
| F     |  FOREIGN KEY constraint               |
| L     |  Log                                  |
| FN    |  Scalar function                      |
| FS    |  Assembly (CLR) scalar-function       |
| FT    |  Assembly (CLR) table-valued function |
| IF    |  In-lined table-function              |
| IT    |  Internal table                       |
| P     |  Stored procedure                     |
| PC    |  Assembly (CLR) stored-procedure      |
| PK    |  PRIMARY KEY constraint (type is K)   |
| RF    |  Replication filter stored procedure  |
| S     |  System table                         |
| SN    |  Synonym                              |
| SQ    |  Service queue                        |
| TA    |  Assembly (CLR) DML trigger           |
| TF    |  Table function                       |
| TR    |  SQL DML Trigger                      |
| TT    |  Table type                           |
| U     |  User table                           |
| UQ    |  UNIQUE constraint (type is K)        |
| V     |  View                                 |
| X     |  Extended stored procedure            |

```

and I could put those into a `CASE` statement, but is there a table I can just join on to lookup that `xtype` description? I know `systypes` isn't that table. I mean, I've just kind of memorized a lot of them, but I'm doing some research on a database and it's foreign to me (i.e. I don't know a ton about it) and so I'd like to build that description into this query without a `CASE` statement:

```sql
select object_name(c.id), c.name, [length], o.xtype from syscolumns c
    join sysobjects o on o.id = c.id
where c.name like '%job%code%'

```

Update

Below is the end result after the answer by SQLMenace. I felt it necessary to place here because it's not just a straight forward `join`.

```sql
select object_name(c.id), c.name, t.name, c.[length], o.xtype, x.name from syscolumns c
    join sysobjects o on o.id = c.id
    join systypes t on t.xtype = c.xtype
    join master..spt_values x on x.name like '%' + o.xtype + '%' and x.type = 'O9T'
where c.name like '%job%code%'
order by c.xtype

```