title: Is there a table that holds the listing of sysobjects.xtype descriptions?
body: <p>According to <a href="https://msdn.microsoft.com/en-us/library/ms177596.aspx" rel="noreferrer">the sysobjects documentation</a>, <code>sysobjects.xtype</code> can be one of these object types:</p>
  
  <pre class="lang-none prettyprint-override"><code>| 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            |
  </code></pre>
  
  <p>and I could put those into a <code>CASE</code> statement, but is there a table I can just join on to lookup that <code>xtype</code> description? I know <code>systypes</code> 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 <code>CASE</code> statement:</p>
  
  <pre class="lang-sql prettyprint-override"><code>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%'
  </code></pre>
  
  Update
  
  <p>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 <code>join</code>.</p>
  
  <pre class="lang-sql prettyprint-override"><code>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
  </code></pre>