SQL Server Sysobjects Types

Note:

a newer post covers SQL Server 2008 through 2019.

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

MS SQL 2005: sys.sysobjects

Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views

MS SQL 2005: Compatibility Views (Transact-SQL)

MS SQL 2008: sys.objects

12 thoughts on “SQL Server Sysobjects Types

  1. Pingback: Checking Naming Conventions of MSSQL Database Objects « devioblog

  2. Pingback: Microsoft – SQLServer – Constraints – Foreign Key – List | Daniel Adeniji's – Learning in the Open

  3. Pingback: SQL Server Sysobjects Types (Version 2019) | devioblog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.