MS SQL Server stores the information about the objects (tables, views, functions, etc) stored in the database in a table, which is accessible by selecting from the sys.objects view. The type of object is given by the column “type”
I collected this cross-version information from Microsoft Docs online, but fixed a couple of historical mistakes there.
Object type: | 2008 | 2012 | 2016 | 2019 | |
---|---|---|---|---|---|
sys.objects.type | |||||
AF | Aggregate function (CLR) | x | x | x | x |
C | CHECK constraint | x | x | x | x |
D | DEFAULT (constraint or stand-alone) | x | x | x | x |
EC | Edge constraint | x | |||
ET | External Table | x | x | ||
F | FOREIGN KEY constraint | x | x | x | x |
FN | SQL scalar function | x | x | x | x |
FS | Assembly (CLR) scalar function | x | x | x | x |
FT | Assembly (CLR) table-valued function | x | x | x | x |
IF | SQL inline table-valued function | x | x | x | x |
IT | Internal table | x | x | x | x |
P | SQL stored procedure | x | x | x | x |
PC | Assembly (CLR) stored procedure | x | x | x | x |
PG | Plan guide | x | x | x | x |
PK | PRIMARY KEY constraint | x | x | x | x |
R | Rule (old-style, stand-alone) | x | x | x | x |
RF | Replication-filter-procedure | x | x | x | x |
S | System base table | x | x | x | x |
SN | Synonym | x | x | x | x |
SO | Sequence object | x | x | x | |
SQ | Service queue | x | x | x | x |
TA | Assembly (CLR) DML trigger | x | x | x | x |
TF | SQL table-valued-function | x | x | x | x |
TR | SQL DML trigger | x | x | x | x |
TT | Table type | x | x | x | x |
U | Table (user-defined) | x | x | x | x |
UQ | UNIQUE constraint | x | x | x | x |
V | View | x | x | x | x |
X | Extended stored procedure | x | x | x | x |
The sys.objects.type_desc column contains the description corresponding to the type:
type | sys.objects.type_desc |
---|---|
AF | AGGREGATE_FUNCTION |
C | CHECK_CONSTRAINT |
D | DEFAULT_CONSTRAINT |
EC | |
ET | |
F | FOREIGN_KEY_CONSTRAINT |
FN | SQL_SCALAR_FUNCTION |
FS | CLR_SCALAR_FUNCTION |
FT | CLR_TABLE_VALUED_FUNCTION |
IF | SQL_INLINE_TABLE_VALUED_FUNCTION |
IT | INTERNAL_TABLE |
P | SQL_STORED_PROCEDURE |
PC | CLR_STORED_PROCEDURE |
PG | PLAN_GUIDE |
PK | PRIMARY_KEY_CONSTRAINT |
R | RULE |
RF | REPLICATION_FILTER_PROCEDURE |
S | SYSTEM_TABLE |
SN | SYNONYM |
SO | SEQUENCE_OBJECT |
SQ | SERVICE_QUEUE |
TA | CLR_TRIGGER |
TF | SQL_TABLE_VALUED_FUNCTION |
TR | SQL_TRIGGER |
TT | TYPE_TABLE |
U | USER_TABLE |
UQ | UNIQUE_CONSTRAINT |
V | VIEW |
X | EXTENDED_STORED_PROCEDURE |
My previous post on this topic only covered SQL Server up to version 2008.
Pingback: SQL Server Sysobjects Types | devioblog