SQL Server Sysobjects Types

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

Advertisements

9 Responses to SQL Server Sysobjects Types

  1. […] 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. […]

  2. Amol says:

    Thanks!

  3. Excellent collection.

  4. Arpan says:

    how to use above keywords……

  5. devio says:

    Arpan, these are not keywords; it’s information stored in the catalog views

  6. Sagar says:

    Thank you..!!

  7. kyledelaney1988 says:

    Is there a system table or view that just lists these types?

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: