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

T-SQL
1
2
3
4
SELECT name
FROM master..spt_values
WHERE type = 'O9T'
AND number  = -1
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

T-SQL
1
2
3
4
5
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
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