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