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 sysobjects view (sql2000) or the sys.objects and sys.sysobjects views (sql2005, sql2008).
The type of object is given by the columns xtype or type, depending on the view.
I collected this cross-version information from MSDN online:
| Object type: | 2000 | 2005 | 2008 | |
| sys.objects.type | ||||
| AF | Aggregate function (CLR) | x | x | |
| C | CHECK constraint | x | x | x |
| D | DEFAULT (constraint or stand-alone) | x | x | x |
| F | FOREIGN KEY constraint | x | x | x |
| FN | SQL scalar function | x | x | x |
| FS | Assembly (CLR) scalar function | x | x | |
| FT | Assembly (CLR) table-valued function | x | x | |
| IF | SQL inline table-valued function | x | x | x |
| IT | Internal table | x | x | |
| P | SQL stored procedure | x | x | x |
| PC | Assembly (CLR) stored procedure | x | x | |
| PG | Plan guide | x | ||
| PK | PRIMARY KEY constraint | x | x | x |
| R | Rule (old-style, stand-alone) | x | x | |
| RF | Replication-filter-procedure | x | x | |
| S | System base table | x | x | x |
| SN | Synonym | x | x | |
| SQ | Service queue | x | x | |
| TA | Assembly (CLR) DML trigger | x | x | |
| TF | SQL table-valued-function | x | x | x |
| TR | SQL DML trigger | x | x | x |
| U | Table (user-defined) | x | x | x |
| UQ | UNIQUE constraint | x | x | x |
| V | View | x | x | x |
| X | Extended stored procedure | 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 |
| 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 |
| SQ | SERVICE_QUEUE |
| TA | CLR_TRIGGER |
| TF | SQL_TABLE_VALUED_FUNCTION |
| TR | SQL_TRIGGER |
| U | USER_TABLE |
| UQ | UNIQUE_CONSTRAINT |
| V | VIEW |
| X | EXTENDED_STORED_PROCEDURE |
References:
MS SQL 2000: sysobjects
Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views
September 4, 2009 at 8:40 |
[...] The naming check can by performed for any other type of database objects by adjusting the comparison of sys.objects.type. See here for the list of SQL Server types. [...]