If you look at the sys.sysobjects view, you will see a xtype column listed

Object type. Can be one of the following object types:

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

However there is no table in SQL Server that holds this info….or is there?

I answered this question today and decided to share here as well

Here is how you can find that info, you can use my favorite table spt_values

SELECT name
FROM master..spt_values
WHERE type = 'O9T'
AND number  = -1

This is the 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

Now if you want to split it into two columns, you can use the LEFT and RIGHT functions together with the PATINDEX function

SELECT LEFT(name,PATINDEX('%:%',name)-1) AS xtype,
RIGHT(name, (LEN(name) - PATINDEX('%:%',name))) AS Description
FROM master..spt_values
WHERE type = 'O9T'
AND number  = -1

Here is the result

xtype  Description
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