Is there a table that holds the listing of sysobjects.xtype descriptions?

sql, sql-server, sql-server-2008

Solution

There is this

SELECT name 
FROM master..spt_values
WHERE type = 'O9T'

Output

AF: aggregate function
AP: application
C : check cns
D : default (maybe cns)
EN: event notification
F : foreign key cns
FN: scalar function
FS: assembly scalar function
FT: assembly table function
IF: inline function
IS: inline scalar function
IT: internal table
L : log
P : stored procedure
PC : assembly stored procedure
PK: primary key cns
R : rule
RF: replication filter proc
S : system table
SN: synonym
SQ: queue
TA: assembly trigger
TF: table function
TR: trigger
U : user table
UQ: unique key cns
V : view
X : extended stored proc
sysobjects.type, reports

Problem

According to the sysobjects documentation, `sysobjects.xtype` can be one of these object types: ``` | 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: ``` 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`. ``` 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 ```

Original source