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